[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
attribute_category ||' '|| c_attribute1 ||' '|| c_attribute2 ||' '|| c_attribute3
||' '|| c_attribute4 ||' '|| c_attribute5 ||' '|| c_attribute6 ||' '|| c_attribute7
||' '|| c_attribute8 ||' '|| c_attribute9 ||' '|| c_attribute10 ||' '|| c_attribute11
||' '|| c_attribute12 ||' '|| c_attribute13 ||' '|| c_attribute14 ||' '|| c_attribute15
||' '|| c_attribute16 ||' '|| c_attribute17 ||' '|| c_attribute18 ||' '|| c_attribute19
||' '|| c_attribute20 ||' '|| d_attribute1 ||' '|| d_attribute2 ||' '|| d_attribute3
||' '|| d_attribute4 ||' '|| d_attribute5 ||' '|| d_attribute6 ||' '|| d_attribute7
||' '|| d_attribute8 ||' '|| d_attribute9 ||' '|| d_attribute10 ||' '|| n_attribute1
||' '|| n_attribute2 ||' '|| n_attribute3 ||' '|| n_attribute4 ||' '|| n_attribute5
||' '|| n_attribute6 ||' '|| n_attribute7 ||' '|| n_attribute8 ||' '|| n_attribute9
||' '|| n_attribute10 as value
FROM mtl_eam_asset_attr_values meaav
WHERE meaav.maintenance_object_id = p_instance_id;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT
msi.concatenated_segments as value
FROM
mtl_system_items_b_kfv msi
, mtl_eam_asset_activities meaa
WHERE
meaa.maintenance_object_id = p_instance_id
AND meaa.maintenance_object_type = 3
AND meaa.asset_activity_id = msi.inventory_item_id
AND rownum = 1;
SELECT
em.meter_name as value
FROM
eam_asset_meters eam
, eam_meters em
WHERE
eam.maintenance_object_id = p_instance_id
AND eam.maintenance_object_type = 3
AND eam.meter_id = em.meter_id;
SELECT
eat.instance_id, cii.last_vld_organization_id, msi.eam_item_type , cii.asset_criticality_code,
Tag_begin_asset_number ||' '|| cii.instance_number ||' '|| cii.instance_description ||' '||
Tag_end_asset_number ||' '|| cii.serial_number ||' '|| msi.concatenated_segments ||' '||
msi.description ||' '|| mck.concatenated_segments ||' '|| msi.description ||' '||
cii.context ||' '|| cii.attribute1 ||' '|| cii.attribute2 ||' '|| cii.attribute3 ||' '||
cii.attribute4 ||' '|| cii.attribute5 ||' '|| cii.attribute6 ||' '|| cii.attribute7 ||' '||
cii.attribute8 ||' '|| cii.attribute9 ||' '|| cii.attribute10 ||' '|| cii.attribute11
||' '|| cii.attribute12 ||' '|| cii.attribute13 ||' '|| cii.attribute14 ||' '||
cii.attribute15 ||' '|| cii.attribute16 ||' '|| cii.attribute17 ||' '|| cii.attribute18
||' '|| cii.attribute19 ||' '|| cii.attribute20 ||' '|| cii.attribute21 ||' '||
cii.attribute22 ||' '|| cii.attribute23 ||' '|| cii.attribute24 ||' '|| cii.attribute25
||' '|| cii.attribute26 ||' '|| cii.attribute27 ||' '|| cii.attribute28 ||' '||
cii.attribute29 ||' '|| cii.attribute30 ||' '|| msi.attribute_category ||' '||
msi.attribute1 ||' '|| msi.attribute2 ||' '|| msi.attribute3 ||' '|| msi.attribute4
||' '|| msi.attribute5 ||' '|| msi.attribute6 ||' '|| msi.attribute7 ||' '||
msi.attribute8 ||' '|| msi.attribute9 ||' '|| msi.attribute10 ||' '|| msi.attribute11
||' '|| msi.attribute12 ||' '|| msi.attribute13 ||' '|| msi.attribute14 ||' '||
msi.attribute15
INTO
l_instance_id, l_org_id, l_eam_item_type, l_criticality_code, l_buffer
FROM
eam_asset_text eat
, csi_item_instances cii
, mtl_system_items_b_kfv msi
, mtl_categories_kfv mck
WHERE
eat.rowid = p_rowid
AND eat.instance_id = cii.instance_id
AND nvl(cii.active_start_date, sysdate-1) <= sysdate
AND nvl(cii.active_end_date, sysdate+1) >= sysdate
AND cii.inventory_item_id = msi.inventory_item_id
AND cii.last_vld_organization_id = msi.organization_id
AND msi.serial_number_control_code <> 1
AND cii.category_id = mck.category_id(+);
SELECT bd.department_code ||' '|| mel.location_codes ||' '|| eomd.accounting_class_code
INTO l_buffer
FROM eam_org_maint_defaults eomd, bom_departments bd, mtl_eam_locations mel,
mtl_parameters mp
WHERE mp.organization_id = l_org_id AND mp.maint_organization_id = eomd.organization_id
AND eomd.object_id = l_instance_id AND eomd.object_type = 50
AND eomd.owning_department_id = bd.department_id (+) AND eomd.area_id = mel.location_id(+);
SELECT
wo.operation_seq_num||' '||bd.department_code as value
FROM WIP_OPERATIONS wo,BOM_DEPARTMENTS bd
WHERE wo.wip_entity_id=p_wip_entity_id
AND wo.department_id=bd.department_id;
SELECT
br.resource_code as value
FROM WIP_OPERATION_RESOURCES wor,BOM_RESOURCES br
WHERE wor.wip_entity_id= p_wip_entity_id
AND wor.resource_id = br.resource_id;
SELECT
ppf.full_name as value
FROM WIP_OP_RESOURCE_INSTANCES wori,
BOM_RESOURCE_EMPLOYEES bre,PER_ALL_PEOPLE_F ppf
WHERE wori.wip_entity_id = p_wip_entity_id
AND wori.instance_id = bre.instance_id
AND bre.person_id = ppf.person_id;
SELECT (Tag_begin_work_order ||' '||we.wip_entity_name||
' '||wdj.description||' '||cii.instance_number||' '||msik.concatenated_segments||' '||
cii.serial_number||' '||msik1.concatenated_segments||' '||Tag_end_work_order||
' '||bd.department_code||' '||PJM_PROJECT.ALL_PROJ_IDTONUM(wdj.project_id)||' '||
PJM_PROJECT.ALL_TASK_IDTONUM(wdj.task_id)) as value
FROM WIP_ENTITIES we,WIP_DISCRETE_JOBS wdj,CSI_ITEM_INSTANCES cii,
EAM_WORK_ORDER_DETAILS ewod,
BOM_DEPARTMENTS bd, MTL_SYSTEM_ITEMS_B_KFV msik, MTL_SYSTEM_ITEMS_B_KFV msik1,
MTL_PARAMETERS mp
WHERE we.wip_entity_id = p_wip_entity_id
AND we.wip_entity_id = wdj.wip_entity_id
AND wdj.wip_entity_id = ewod.wip_entity_id
AND wdj.owning_department = bd.department_id(+)
AND msik1.organization_id(+)=wdj.organization_id
AND msik1.inventory_item_id(+)=wdj.primary_item_id
AND msik.inventory_item_id=NVL(wdj.rebuild_item_id,wdj.asset_group_id)
AND msik.organization_id = mp.organization_id
AND cii.instance_id(+)=DECODE(wdj.maintenance_object_type,p_maint_obj_type,wdj.maintenance_object_id,NULL)
AND mp.maint_organization_id = p_org_id;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT NVL(ewst.user_defined_status,flv.meaning) as value
FROM EAM_WORK_ORDER_DETAILS ewod, EAM_WO_STATUSES_B ewsb,
EAM_WO_STATUSES_TL ewst,FND_LOOKUP_VALUES flv
WHERE ewod.wip_entity_id = p_wip_entity_id
AND ewod.user_defined_status_id = ewsb.status_id
AND ewsb.status_id = ewst.status_id(+)
AND flv.lookup_type(+) = 'WIP_JOB_STATUS'
AND flv.lookup_code(+) = ewsb.status_id;
SELECT
ewot.wip_entity_id,ewot.organization_id,wdj.priority,wdj.work_order_type,
wdj.activity_type,wdj.activity_cause,wdj.activity_source
INTO
l_wip_entity_id,l_org_id,l_priority,l_work_order_type,l_activity_type,l_activity_cause,l_activity_source
FROM
eam_work_order_text ewot,wip_discrete_jobs wdj
WHERE
ewot.rowid = p_rowid
AND ewot.wip_entity_id = wdj.wip_entity_id;
, p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
)
IS
l_text_ins VARCHAR2(1);
SELECT count(instance_id) INTO l_count
FROM eam_asset_text WHERE instance_id = p_instance_id AND rownum = 1;
IF ( p_event = 'UPDATE' OR p_event = 'INSERT' ) THEN
IF (l_count = 1) THEN
UPDATE eam_asset_text
SET text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE instance_id = p_instance_id;
INSERT INTO eam_asset_text
(
instance_id
, text
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values (
p_instance_id
, l_text_ins
, SYSDATE
, fnd_global.user_id
, SYSDATE
, DECODE(p_last_updated_by, FND_API.G_MISS_NUM, fnd_global.user_id, p_last_updated_by)
, DECODE(p_last_update_login, FND_API.G_MISS_NUM, fnd_global.login_id, p_last_update_login));
ELSIF ( p_event = 'DELETE' ) THEN
DELETE FROM eam_asset_text
WHERE p_instance_id = p_instance_id;
PROCEDURE Process_Asset_Update_Event
(
p_event IN VARCHAR2 DEFAULT NULL
, p_instance_id IN NUMBER
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
)
IS
l_eam VARCHAR2(5);
, p_last_update_date
, p_last_updated_by
, p_last_update_login );
SELECT count(*) into l_count
FROM all_indexes
WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
AND table_name = l_table AND index_name = l_index
AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
END Process_Asset_Update_Event;
, p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
)
IS
l_text_ins VARCHAR2(1) := '1';
IF ( p_event = 'UPDATE' ) THEN
UPDATE eam_work_order_text
SET text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id;
ELSIF ( p_event = 'INSERT' ) THEN
INSERT INTO eam_work_order_text
(
organization_id
, wip_entity_id
, text
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values
(
p_organization_id,
p_wip_entity_id,
l_text_ins,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
);
, p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
)
IS
l_eam VARCHAR2(5);
p_last_update_date,
p_last_updated_by,
p_last_update_login
);
SELECT count(*) into l_count
FROM all_indexes
WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
AND table_name = l_table AND index_name = l_index
AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
***** Procedure called when a status code is updated from User Defined Statuses form
***/
PROCEDURE Process_Status_Update_Event
(
p_event IN VARCHAR2 DEFAULT NULL,
p_status_id IN NUMBER
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_eam VARCHAR2(5);
SELECT ewod.wip_entity_id,ewod.organization_id
FROM EAM_WORK_ORDER_DETAILS ewod
WHERE ewod.user_defined_status_id = l_status_id;
('UPDATE',
wo.wip_entity_id,
wo.organization_id,
p_last_update_date,
p_last_updated_by,
p_last_update_login
);
SELECT count(*) into l_count
FROM all_indexes
WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
AND table_name = l_table AND index_name = l_index
AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
END Process_Status_Update_Event;
SELECT SUBSTR(VALUE,0,1) into l_DB_Numeric_Character
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';