[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row(
p_log_id IN number := NULL,
p_api_version IN number := 1.0,
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_event_date IN date := sysdate,
p_event_type IN varchar2 := NULL,
p_event_id IN number := NULL,
p_organization_id IN number := NULL,
p_instance_id IN number,
p_comments IN varchar2 := NULL,
p_reference IN varchar2 := NULL,
p_ref_id IN number := NULL,
p_operable_flag IN number := NULL,
p_reason_code IN number := NULL,
p_resource_id IN number := NULL,
p_equipment_gen_object_id IN number := NULL,
p_source_log_id IN number := NULL,
p_instance_number IN varchar2 := NULL,
p_downcode IN number := NULL,
p_expected_up_date IN date := NULL,
p_employee_id IN number := NULL,
p_department_id IN number := NULL,
p_attribute_category IN varchar2 := NULL,
p_attribute1 IN varchar2 := NULL,
p_attribute2 IN varchar2 := NULL,
p_attribute3 IN varchar2 := NULL,
p_attribute4 IN varchar2 := NULL,
p_attribute5 IN varchar2 := NULL,
p_attribute6 IN varchar2 := NULL,
p_attribute7 IN varchar2 := NULL,
p_attribute8 IN varchar2 := NULL,
p_attribute9 IN varchar2 := NULL,
p_attribute10 IN varchar2 := NULL,
p_attribute11 IN varchar2 := NULL,
p_attribute12 IN varchar2 := NULL,
p_attribute13 IN varchar2 := NULL,
p_attribute14 IN varchar2 := NULL,
p_attribute15 IN varchar2 := NULL,
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) := 'insert_row';
SELECT mp.maint_organization_id, cii.operational_log_flag
INTO l_organization_id, l_status
FROM mtl_parameters mp, csi_item_instances cii
WHERE mp.organization_id = cii.last_vld_organization_id
AND cii.instance_id = p_instance_id;
SELECT event_id INTO l_var2 FROM eam_control_event
WHERE event_type = p_event_type
AND event_id= p_event_id
AND organization_id=l_organization_id;
SELECT eam_asset_log_s.nextval INTO l_log_id FROM dual;
INSERT
INTO eam_asset_log
(
log_id,
event_date,
event_type,
event_id,
organization_id,
instance_id,
reference,
ref_id,
operable,
reason_code,
resource_id,
comments,
down_code,
resource_serial_number,
expected_up_date,
source_log_id,
employee_id,
department_id,
equipment_gen_object_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_log_id,
p_event_date,
p_event_type,
p_event_id,
p_organization_id,
p_instance_id,
p_reference,
p_ref_id,
p_operable_flag,
p_reason_code,
p_resource_id,
p_comments,
p_downcode,
p_instance_number,
p_expected_up_date,
p_source_log_id,
p_employee_id,
p_department_id,
p_equipment_gen_object_id,
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,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
)
;
END insert_row;
SELECT lookup_code INTO l_status FROM mfg_lookups
WHERE lookup_type = p_event_type AND
lookup_code = p_event_id AND
enabled_flag = 'Y' AND
p_event_date >= NVL(start_date_active, p_event_date) AND
p_event_date <= NVL(end_date_active,sysdate);
SELECT instance_id INTO l_status FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT lookup_code INTO l_status FROM mfg_lookups
WHERE lookup_type = 'SYS_YES_NO' AND
enabled_flag = 'Y' AND
p_event_date >= NVL(start_date_active, p_event_date) AND
p_event_date <= NVL(end_date_active,sysdate) AND
lookup_code = p_operable_flag;
SELECT lookup_code INTO l_status FROM mfg_lookups
WHERE lookup_type = 'EAM_LOG_REASON_CODE' AND
enabled_flag = 'Y' AND
p_event_date >= NVL(start_date_active,p_event_date) AND
p_event_date <= NVL(end_date_active,sysdate) AND
lookup_code = p_reason_code;
SELECT resource_id INTO l_status FROM bom_resources_v
WHERE resource_id = p_resource_id;
SELECT downcode INTO l_status FROM bom_resource_downcodes
WHERE downcode = p_downcode AND
resource_id = p_resource_id ;
PROCEDURE delete_row(
errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY number,
p_start_date IN varchar2,
p_end_date IN varchar2,
p_asset_group IN number,
p_instance_id IN number,
p_event_type IN varchar2,
p_event_id IN number,
p_resource_id IN number,
p_organization_id IN number,
p_equipment_gen_object_id IN number
)
IS
l_statement varchar2(2000);
select nvl(maint_organization_id, p_organization_id) into l_organization_id
from mtl_parameters
where organization_id = p_organization_id;
l_statement := 'DELETE FROM eam_asset_log eal WHERE eal.organization_id = :1';
l_statement := l_statement || ' AND EXISTS (SELECT cii.instance_id FROM csi_item_instances cii WHERE cii.inventory_item_id = '||p_asset_group||' AND cii.instance_id = eal.instance_id)';
END delete_row;
PROCEDURE instance_update_event(
p_api_version IN number := 1.0,
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_event_date IN date,
p_event_type IN varchar2 := 'EAM_SYSTEM_EVENTS',
p_event_id IN number := NULL,
p_instance_id IN number,
p_ref_id IN number,
p_organization_id IN number := NULL,
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) :='instance_update_event';
SELECT mp.maint_organization_id INTO l_organization_id
FROM mtl_parameters mp, csi_item_instances cii
WHERE mp.organization_id = cii.last_vld_organization_id
AND cii.instance_id = p_instance_id;
SELECT instance_history_id INTO l_status
FROM csi_item_instances_H
WHERE instance_history_id = p_ref_id
AND (nvl(old_location_id,1) <> nvl(new_location_id,1));
SELECT instance_history_id INTO l_status1
FROM csi_item_instances_h
WHERE instance_history_id = p_ref_id AND
old_active_end_date IS NULL and
new_active_end_date IS NOT NULL;
SELECT instance_history_id INTO l_status2
FROM csi_item_instances_h
WHERE instance_history_id = p_ref_id AND
old_active_end_date is not null
AND new_active_end_date IS NULL;
SELECT old_location_id||'->'||new_location_id INTO l_reference
FROM csi_item_instances_H
WHERE instance_history_id = p_ref_id;
SELECT to_char(last_updated_by) INTO l_reference
FROM csi_item_instances_h
WHERE instance_history_id = p_ref_id;
SELECT to_char(last_updated_by) INTO l_reference
FROM csi_item_instances_H
WHERE instance_history_id = p_ref_id;
EAM_ASSET_LOG_PVT.insert_row(
p_event_date => p_event_date,
p_event_type => p_event_type,
p_event_id => l_event_id,
p_organization_id => l_organization_id,
p_instance_id => p_instance_id,
p_reference => l_reference,
p_ref_id => p_ref_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END instance_update_event;
PROCEDURE insert_meter_log(
p_api_version IN number := 1.0,
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_event_date IN date := sysdate,
p_instance_id IN number := NULL,
p_ref_id IN number,
p_attribute_category IN varchar2 := NULL,
p_attribute1 IN varchar2 := NULL,
p_attribute2 IN varchar2 := NULL,
p_attribute3 IN varchar2 := NULL,
p_attribute4 IN varchar2 := NULL,
p_attribute5 IN varchar2 := NULL,
p_attribute6 IN varchar2 := NULL,
p_attribute7 IN varchar2 := NULL,
p_attribute8 IN varchar2 := NULL,
p_attribute9 IN varchar2 := NULL,
p_attribute10 IN varchar2 := NULL,
p_attribute11 IN varchar2 := NULL,
p_attribute12 IN varchar2 := NULL,
p_attribute13 IN varchar2 := NULL,
p_attribute14 IN varchar2 := NULL,
p_attribute15 IN varchar2 := NULL,
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) := 'insert_meter_log';
SELECT cii.instance_id, cii.instance_number, cii.last_vld_organization_id org_id
FROM csi_counter_associations caa, csi_item_instances cii
WHERE caa.counter_id = p_ref_id AND
cii.instance_id = caa.source_object_id;
SELECT counter_id INTO l_status
FROM csi_counters_b
WHERE counter_id = p_ref_id AND
p_event_date >= NVL(start_date_active, p_event_date) AND
p_event_date <= NVL(end_date_active, p_event_date);
SELECT name INTO l_reference
FROM csi_counters_tl
WHERE language = userenv('Lang')
AND counter_id= p_ref_id;
SELECT maint_organization_id INTO l_organization_id
FROM mtl_parameters
WHERE organization_id = l_cmetid.org_id;
SELECT eam_asset_log_s.nextval INTO l_log_id FROM dual;
EAM_ASSET_LOG_PVT.insert_row(
p_event_date => p_event_date,
p_event_type => l_event_type,
p_event_id => l_event_id,
p_organization_id => l_organization_id,
p_instance_id => l_instance_id,
p_reference => l_reference,
p_ref_id => p_ref_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END insert_meter_log;