[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MP.organization_code
FROM mtl_parameters MP
WHERE mp.organization_id = p_org_id ;
SELECT msikfv.inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_kfv msikfv, mtl_parameters mp
WHERE msikfv.organization_id = mp.organization_id
AND mp.maint_organization_id = l_organization_id
AND msikfv.concatenated_segments = l_concatenated_segments
AND rownum = 1;
SELECT gsob.period_set_name
INTO l_period_set_name
FROM hr_organization_information ood,
gl_sets_of_books gsob
WHERE ood.organization_id = l_organization_id
AND to_number(ood.org_information1) = gsob.set_of_books_id
AND ood.org_information_context||'' = 'Accounting Information';
select nvl(max(period_name),'') into l_period_name from gl_periods where start_date <= sysdate and (end_date+1) >= sysdate and period_set_name = l_period_set_name;
select currency_code curr_code into l_currency from hr_organization_information, gl_sets_of_books where set_of_books_id = ORG_INFORMATION1 and organization_id = l_organization_id and ORG_INFORMATION_CONTEXT = 'Accounting Information';
SELECT SERIAL_NUMBER_GENERATION
INTO l_serial_generation
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT AUTO_SERIAL_ALPHA_PREFIX,
START_AUTO_SERIAL_NUMBER
INTO l_asset_prefix,
l_asset_number
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id
FOR UPDATE OF START_AUTO_SERIAL_NUMBER;
SELECT AUTO_SERIAL_ALPHA_PREFIX,
START_AUTO_SERIAL_NUMBER
INTO l_asset_prefix,
l_asset_number
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT SERIAL_NUMBER_TYPE
INTO l_serial_number_type
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT count(*)
INTO l_count
FROM MTL_SERIAL_NUMBERS
WHERE serial_number = l_concat_asset_number
and inventory_item_id=p_inventory_item_id;
select count(*) into l_count
from
MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
where
msi.organization_id=mp.organization_id and
mp.serial_number_generation = 2 and
msi.inventory_item_id=p_inventory_item_id and
msi.auto_serial_alpha_prefix=l_asset_prefix and
msi.start_auto_serial_number-1=l_asset_number ;
SELECT count(*)
INTO l_count
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_concat_asset_number
AND CURRENT_ORGANIZATION_ID = p_organization_id;
SELECT count(*)
INTO l_count
FROM MTL_SERIAL_NUMBERS S,
MTL_PARAMETERS P
WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
AND S.SERIAL_NUMBER = l_concat_asset_number
AND P.SERIAL_NUMBER_TYPE = 3;
select count(*) into l_count
from
MTL_PARAMETERS mp
where
mp.organization_id=p_organization_id and
mp.auto_serial_alpha_prefix=l_asset_prefix and
mp.start_auto_serial_number-1=l_asset_number;
SELECT count(*)
INTO l_count
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_concat_asset_number;
select count(*) into l_count
from
MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
where
msi.organization_id=mp.organization_id and
mp.serial_number_generation = 2 and
msi.inventory_item_id=p_inventory_item_id and
msi.auto_serial_alpha_prefix=l_asset_prefix and
msi.start_auto_serial_number-1=l_asset_number;
select count(*) into l_count
from
MTL_PARAMETERS mp
where
mp.serial_number_generation = 1 and
mp.auto_serial_alpha_prefix=l_asset_prefix and
mp.start_auto_serial_number-1=l_asset_number;
select base_item_id
into l_base_item_id
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select count(*) into l_count
from mtl_serial_numbers msn1, mtl_system_items msi1
where msn1.serial_number = l_concat_asset_number
and msn1.inventory_item_id = msi1.inventory_item_id
and msn1.current_organization_id = msi1.organization_id
and msi1.base_item_id = l_base_item_id;
SELECT count(*)
INTO l_count
FROM MTL_SERIAL_NUMBERS
WHERE serial_number = l_concat_asset_number
AND inventory_item_id=p_inventory_item_id;
SELECT count(*) INTO l_count
FROM
MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
WHERE
msi.organization_id=mp.organization_id AND
mp.serial_number_generation = 2 AND
msi.inventory_item_id=p_inventory_item_id AND
msi.auto_serial_alpha_prefix=l_asset_prefix AND
msi.start_auto_serial_number-1=l_asset_number ;
UPDATE MTL_PARAMETERS
SET AUTO_SERIAL_ALPHA_PREFIX = l_asset_prefix,
START_AUTO_SERIAL_NUMBER = l_asset_number
WHERE ORGANIZATION_ID = p_organization_id;
UPDATE MTL_SYSTEM_ITEMS
SET AUTO_SERIAL_ALPHA_PREFIX = l_asset_prefix,
START_AUTO_SERIAL_NUMBER = l_asset_number
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
select count(*)
into x_boolean
from org_access_view oav,
mtl_parameters mp,
wip_eam_parameters wep
where oav.organization_id = mp.organization_id
and oav.responsibility_id = p_resp_id
and oav.resp_application_id = p_resp_app_id
and NVL(mp.eam_enabled_flag,'N') = 'Y'
and oav.organization_id = p_org_id
and wep.organization_id = p_org_id;
select count(*)
into x_boolean
from wip_eam_parameters wep
where wep.organization_id = p_org_id;
select meaning
into l_meaning
from mfg_lookups
where lookup_type = p_lookup_type
and lookup_code=p_lookup_code;
select nvl(cia.inventory_item_id,0)
into l_inventory_item_id
from cs_incidents_all_b cia
where cia.incident_id = p_service_request_id;
select concatenated_segments
into l_item_name
from mtl_system_items_kfv msi
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id;
select count(*) into l_count
from mtl_system_items
where inventory_item_id=p_inventory_item_id
and organization_id=p_organization_id
and eam_item_type=p_eam_item_type;
select count(*) into l_count
from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
where cii.last_vld_organization_id=mp.organization_id
and msi.organization_id = cii.last_vld_organization_id
and mp.maint_organization_id = p_organization_id
and cii.inventory_item_id=p_inventory_item_id
and cii.serial_number=p_serial_number
and cii.inventory_item_id=msi.inventory_item_id
and msi.eam_item_type=p_eam_item_type;
select count(*) into l_count
from bom_departments
where department_id=p_department_id
and organization_id=p_organization_id;
select count(*) into l_count
from mtl_eam_locations
where location_id=p_location_id;
select count(*) into l_count
from mtl_eam_locations
where organization_id=p_organization_id
and location_id=p_location_id
and (END_DATE >= SYSDATE OR END_DATE IS NULL);
select count(*) into l_count
from WIP_ACCOUNTING_CLASSES
where class_code = p_wip_accounting_class_code
and organization_id = p_organization_id
and class_type = 6; -- WIP_CLASS_TYPE=Maintenance Accounting Class
select count(*) into l_count
from csi_counters_b
where counter_id=p_meter_id;
select count(*) into l_count
from csi_counters_b
where counter_id=p_meter_id;
select count(*) into l_count
from csi_counter_template_b
where counter_id=p_meter_id;
select count(*) into l_count
from mfg_lookups
where
lookup_type=p_lookup_type and
lookup_code=p_lookup_code;
select count(*) into l_count
from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
where
msi.organization_id=cii.last_vld_organization_id and
msi.inventory_item_id=cii.inventory_item_id and
cii.instance_id = p_maintenance_object_id and
msi.eam_item_type=p_eam_item_type;
select count(*) into l_count
from mtl_system_items msi, mtl_parameters mp
where
msi.inventory_item_id=p_maintenance_object_id
and msi.eam_item_type=p_eam_item_type
and msi.organization_id=mp.organization_id
and mp.maint_organization_id = p_organization_id;
select msn.current_organization_id,
msn.inventory_item_id,
msn.serial_number
into l_organization_id, l_inventory_item_id, l_serial_number
from mtl_serial_numbers msn, mtl_system_items msi
where
msn.gen_object_id=p_maintenance_object_id and
msi.inventory_item_id=msn.inventory_item_id and
msi.organization_id=msn.current_organization_id and
msi.eam_item_type=p_eam_item_type;
select organization_id,
inventory_item_id
into l_organization_id, l_inventory_item_id
from mtl_system_items
where
organization_id=p_organization_id and
inventory_item_id=p_maintenance_object_id
and eam_item_type=p_eam_item_type;
select instance_id
into x_maintenance_object_id
from csi_item_instances
where inventory_item_id=p_inventory_item_id
and serial_number=p_serial_number;
select inventory_item_id
into x_maintenance_object_id
from mtl_system_items
where inventory_item_id=p_inventory_item_id
and eam_item_type in (1,3)
and rownum = 1;
SELECT mp.maint_organization_id, cii.inventory_item_id, cii.serial_number
INTO x_organization_id, x_inventory_item_id, x_serial_number
FROM csi_item_instances cii, mtl_parameters mp
WHERE cii.instance_id=p_maintenance_object_id
AND cii.last_vld_organization_id = mp.organization_id;
select inventory_item_id
into x_inventory_item_id
from mtl_system_items
where inventory_item_id=p_maintenance_object_id
and eam_item_type in (1,3)
and rownum = 1;
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_todays_work,
l_todays_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
wip_op_resource_instances wori,
bom_resource_employees bre,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = wori.organization_id
AND wor.wip_entity_id = wori.wip_entity_id
AND wor.operation_seq_num = wori.operation_seq_num
AND wor.resource_seq_num = wori.resource_seq_num
AND wori.serial_number IS NULL
AND wori.instance_id = bre.instance_id
AND wor.organization_id = bre.organization_id
AND wor.resource_id = bre.resource_id
AND sysdate >= bre.effective_start_date
AND sysdate <= bre.effective_end_date
AND wori.organization_id = res.organization_id (+)
AND wori.wip_entity_id = res.wip_entity_id (+)
AND wori.operation_seq_num = res.operation_seq_num (+)
AND wori.resource_seq_num = res.resource_seq_num (+)
AND wori.instance_id = res.instance_id (+)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND bre.organization_id = p_organization_id
AND bre.person_id = p_employee_id
AND TO_CHAR(wo.first_unit_start_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate),'yyyy-mm-dd') = substr(l_current_date,1,10);
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_overdue_work,
l_overdue_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
wip_op_resource_instances wori,
bom_resource_employees bre,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = wori.organization_id
AND wor.wip_entity_id = wori.wip_entity_id
AND wor.operation_seq_num = wori.operation_seq_num
AND wor.resource_seq_num = wori.resource_seq_num
AND wori.serial_number IS NULL
AND wori.instance_id = bre.instance_id
AND wor.organization_id = bre.organization_id
AND wor.resource_id = bre.resource_id
AND sysdate >= bre.effective_start_date
AND sysdate <= bre.effective_end_date
AND wori.organization_id = res.organization_id (+)
AND wori.wip_entity_id = res.wip_entity_id (+)
AND wori.operation_seq_num = res.operation_seq_num (+)
AND wori.resource_seq_num = res.resource_seq_num (+)
AND wori.instance_id = res.instance_id (+)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND bre.organization_id = p_organization_id
AND bre.person_id = p_employee_id
AND wo.last_unit_completion_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate) < to_date(substr(p_current_date,1,10), 'yyyy-mm-dd');
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_open_work,
l_open_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
wip_op_resource_instances wori,
bom_resource_employees bre,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = wori.organization_id
AND wor.wip_entity_id = wori.wip_entity_id
AND wor.operation_seq_num = wori.operation_seq_num
AND wor.resource_seq_num = wori.resource_seq_num
AND wori.serial_number IS NULL
AND wori.instance_id = bre.instance_id
AND wor.organization_id = bre.organization_id
AND wor.resource_id = bre.resource_id
AND sysdate >= bre.effective_start_date
AND sysdate <= bre.effective_end_date
AND wori.organization_id = res.organization_id (+)
AND wori.wip_entity_id = res.wip_entity_id (+)
AND wori.operation_seq_num = res.operation_seq_num (+)
AND wori.resource_seq_num = res.resource_seq_num (+)
AND wori.instance_id = res.instance_id (+)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND bre.organization_id = p_organization_id
AND bre.person_id = p_employee_id;
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_todays_work, l_todays_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
bom_resources br,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage woru
WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
(SELECT 1
FROM wip_op_resource_instances wori
WHERE woru.wip_entity_id = wori.wip_entity_id
AND woru.operation_seq_num = wori.operation_seq_num
AND woru.resource_seq_num = wori.resource_seq_num
)
)
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = res.organization_id (+)
AND wor.wip_entity_id = res.wip_entity_id (+)
AND wor.operation_seq_num = res.operation_seq_num (+)
AND wor.resource_seq_num = res.resource_seq_num (+)
AND wor.resource_id = br.resource_id
AND wor.organization_id = br.organization_id
AND br.resource_type = 2
AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND we.organization_id = p_organization_id
AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
AND ( (p_department_id IS NOT NULL)
OR EXISTS
(
SELECT 1
FROM bom_resource_employees bre,
bom_dept_res_instances bdri,
bom_departments bd
WHERE bre.person_id = p_employee_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date >= sysdate
AND bre.resource_id = bdri.resource_id
AND bre.instance_id = bdri.instance_id
AND bdri.department_id = bd.department_id
AND bre.organization_id = bd.organization_id
AND bre.organization_id = p_organization_id
AND bd.department_id = wo.department_id
)
)
AND TO_CHAR(wo.first_unit_start_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate),'yyyy-mm-dd') = substr(l_current_date,1,10);
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_overdue_work, l_overdue_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
bom_resources br,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage woru
WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
(SELECT 1
FROM wip_op_resource_instances wori
WHERE woru.wip_entity_id = wori.wip_entity_id
AND woru.operation_seq_num = wori.operation_seq_num
AND woru.resource_seq_num = wori.resource_seq_num
)
)
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = res.organization_id (+)
AND wor.wip_entity_id = res.wip_entity_id (+)
AND wor.operation_seq_num = res.operation_seq_num (+)
AND wor.resource_seq_num = res.resource_seq_num (+)
AND wor.resource_id = br.resource_id
AND wor.organization_id = br.organization_id
AND br.resource_type = 2
AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND we.organization_id = p_organization_id
AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
AND ( (p_department_id IS NOT NULL)
OR EXISTS
(
SELECT 1
FROM bom_resource_employees bre,
bom_dept_res_instances bdri,
bom_departments bd
WHERE bre.person_id = p_employee_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date >= sysdate
AND bre.resource_id = bdri.resource_id
AND bre.instance_id = bdri.instance_id
AND bdri.department_id = bd.department_id
AND bre.organization_id = bd.organization_id
AND bre.organization_id = p_organization_id
AND bd.department_id = wo.department_id
)
)
AND wo.last_unit_completion_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' )-sysdate) < to_date(substr(p_current_date,1,10), 'yyyy-mm-dd');
SELECT count(*) ,
decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
INTO l_open_work, l_open_work_duration
FROM wip_entities we,
wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
bom_resources br,
(SELECT wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id,
SUM(completion_date - start_date) usage
FROM wip_operation_resource_usage woru
WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
(SELECT 1
FROM wip_op_resource_instances wori
WHERE woru.wip_entity_id = wori.wip_entity_id
AND woru.operation_seq_num = wori.operation_seq_num
AND woru.resource_seq_num = wori.resource_seq_num
)
)
GROUP BY wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
instance_id) res
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.organization_id = wo.organization_id
AND we.wip_entity_id = wo.wip_entity_id
AND wo.organization_id = wor.organization_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num
AND wor.organization_id = res.organization_id (+)
AND wor.wip_entity_id = res.wip_entity_id (+)
AND wor.operation_seq_num = res.operation_seq_num (+)
AND wor.resource_seq_num = res.resource_seq_num (+)
AND wor.resource_id = br.resource_id
AND wor.organization_id = br.organization_id
AND br.resource_type = 2
AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
AND we.entity_type = 6
AND wdj.status_type = 3
AND we.organization_id = p_organization_id
AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
AND ( (p_department_id IS NOT NULL)
OR EXISTS
(
SELECT 1
FROM bom_resource_employees bre,
bom_dept_res_instances bdri,
bom_departments bd
WHERE bre.person_id = p_employee_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date >= sysdate
AND bre.resource_id = bdri.resource_id
AND bre.instance_id = bdri.instance_id
AND bdri.department_id = bd.department_id
AND bre.organization_id = bd.organization_id
AND bre.organization_id = p_organization_id
AND bd.department_id = wo.department_id
)
) ;
PROCEDURE insert_into_wori (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_organization_id IN VARCHAR2
,p_employee_id IN VARCHAR2
,p_wip_entity_id IN VARCHAR2
,p_operation_seq_num IN VARCHAR2
,p_resource_seq_num IN VARCHAR2
,p_resource_id IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_wip_entity_name OUT NOCOPY VARCHAR2)
IS
-- Input Tables
l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wori';
SAVEPOINT insert_into_wori_pvt;
select instance_id
into l_instance_id
from bom_resource_employees
where resource_id = p_resource_id
and organization_id = p_organization_id
and person_id = p_employee_id;
select wip_entity_name
into l_wip_entity_name
from wip_entities
where wip_entity_id = l_eam_res_inst_rec.WIP_ENTITY_ID
and organization_id = l_eam_res_inst_rec.ORGANIZATION_ID;
, p_debug_filename => 'insertwori.log'
, p_output_dir => l_output_dir
);
ROLLBACK TO insert_into_wori_pvt;
ROLLBACK TO insert_into_wori_pvt;
ROLLBACK TO insert_into_wori_pvt;
END insert_into_wori;
select to_char(employee_id)
into l_person_id
from fnd_user
where user_id = l_user_id;
select department_id into l_dept_id
from bom_departments
where department_code = p_dept_code
and organization_id = p_org_id;
select bd.department_id into l_dept_id
from bom_departments bd, mtl_parameters mp
where bd.department_code = p_dept_code
and mp.organization_code = p_org_code
and bd.organization_id = mp.organization_id;
select instance_id into l_instance_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
;
select gen_object_id into l_gen_object_id
from mtl_serial_numbers
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
;
select serial_number, inventory_item_id, current_organization_id
into l_serial_number, l_inventory_item_id, l_organization_id
from mtl_serial_numbers
where gen_object_id = p_gen_object_id;
SELECT 'Y'
INTO l_hr_exists
FROM DUAL
WHERE EXISTS
(SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE mog.object_id = l_gen_object_id
-- Fix for bug 2219479. We do not allow assets that are
-- a child or a parent in the future to be deactivated.
-- hence the check for start_date_active is removed
AND sysdate <= nvl(mog.end_date_active(+), sysdate))
OR EXISTS
(SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE mog.parent_object_id = l_gen_object_id
AND sysdate <= nvl(mog.end_date_active(+), sysdate));
SELECT 'Y'
INTO l_routes_exists
FROM DUAL
WHERE EXISTS
(SELECT mena.network_association_id
FROM mtl_eam_network_assets mena
WHERE mena.maintenance_object_type = 3
AND mena.maintenance_object_id = l_instance_id
AND sysdate >= nvl(mena.start_date_active(+), sysdate)
AND sysdate <= nvl(mena.end_date_active(+), sysdate));
SELECT 'Y'
INTO l_wo_exists
FROM DUAL
WHERE EXISTS
(SELECT wdj.wip_entity_id
FROM wip_discrete_jobs wdj
WHERE wdj.status_type not in (4, 5, 7, 12)
AND wdj.maintenance_object_type = 3
AND wdj.maintenance_object_id = l_instance_id
AND wdj.organization_id = l_organization_id)
OR EXISTS
(SELECT wewr.asset_number
FROM wip_eam_work_requests wewr
WHERE wewr.work_request_status_id not in (5, 6)
AND wewr.organization_id = l_organization_id
AND wewr.maintenance_object_type = 3
AND wewr.maintenance_object_id = l_instance_id);
SELECT 'Y'
into l_sr_exists
from dual
where exists
(
select cia.incident_id from cs_incidents_vl_sec cia,CS_INCIDENT_STATUSES_VL cis
where cia.customer_product_id = l_instance_id
and cia.incident_status_id = cis.incident_status_id
and nvl(cis.close_flag,'N') <> 'Y'
and cis.language = userenv('lang')
);
eam_asset_number_pvt.update_asset(
P_API_VERSION => 1.0
,p_commit => p_commit
,p_instance_id => l_instance_id
,P_INVENTORY_ITEM_ID => l_inventory_item_id
,P_SERIAL_NUMBER => l_serial_number
,P_ORGANIZATION_ID => l_organization_id
,p_active_end_date => sysdate
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
SELECT
msi.lot_control_code,
msi.serial_number_control_code,
msi.revision_qty_control_code
FROM mtl_system_items_b msi
WHERE msi.organization_id = c_organization_id
AND msi.inventory_item_id = c_inventory_item_id;
SELECT NVL(SUM(QUANTITY),0)
into l_qoh
FROM MTL_SECONDARY_INVENTORIES MSS,
MTL_ITEM_QUANTITIES_VIEW MOQ,
MTL_SYSTEM_ITEMS MSI
WHERE MOQ.ORGANIZATION_ID = p_org_id
AND MSI.ORGANIZATION_ID = p_org_id
AND MSS.ORGANIZATION_ID = p_org_id
AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
AND MSS.AVAILABILITY_TYPE = 1;
SELECT count(*) INTO l_count FROM eam_linear_locations
WHERE eam_linear_id = p_eam_linear_id;
select msn.current_organization_id, msn.descriptive_text, msn.current_status
into l_organization_id, l_description, l_current_status
from mtl_serial_numbers msn
where msn.inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
l_eam_wo_rec.asset_group_id)
and msn.serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
l_eam_wo_rec.asset_number);
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATED_BY => l_eam_wo_rec.user_id
,P_CREATION_DATE => sysdate
,P_CREATED_BY => l_eam_wo_rec.user_id
,P_LAST_UPDATE_LOGIN => l_eam_wo_rec.user_id
,X_OBJECT_ID => l_instance_id
,X_RETURN_STATUS => l_x_asset_return_status
,X_MSG_COUNT => l_x_asset_msg_count
,X_MSG_DATA => l_x_asset_msg_data
);
select cii.instance_id
into l_instance_id
from csi_item_instances cii
where inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
l_eam_wo_rec.asset_group_id)
and serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
l_eam_wo_rec.asset_number);
select gen_object_id into l_gen_object_id
from mtl_serial_numbers msn, csi_item_instances cii
where msn.inventory_item_id = cii.inventory_item_id
and msn.serial_number = cii.serial_number
and cii.instance_id = p_maintenance_object_id;
SELECT 'Y'
INTO l_hr_exists
FROM DUAL
WHERE EXISTS
(SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE mog.object_id = l_gen_object_id
-- Fix for bug 2219479. We do not allow assets that are
-- a child or a parent in the future to be deactivated.
-- hence the check for start_date_active is removed
AND sysdate <= nvl(mog.end_date_active(+), sysdate))
OR EXISTS
(SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE mog.parent_object_id = l_gen_object_id
AND sysdate <= nvl(mog.end_date_active(+), sysdate));
SELECT 'Y'
INTO l_routes_exists
FROM DUAL
WHERE EXISTS
(SELECT mena.network_association_id
FROM mtl_eam_network_assets mena
WHERE mena.maintenance_object_type =3
AND mena.maintenance_object_id = p_maintenance_object_id
AND sysdate >= nvl(mena.start_date_active(+), sysdate)
AND sysdate <= nvl(mena.end_date_active(+), sysdate));
SELECT 'Y'
INTO l_wo_exists
FROM DUAL
WHERE EXISTS
(SELECT wdj.wip_entity_id
FROM wip_discrete_jobs wdj
WHERE wdj.status_type not in (4, 5, 7, 12)
AND wdj.maintenance_object_type = 3
AND wdj.maintenance_object_id = p_maintenance_object_id
)
OR EXISTS
(SELECT wewr.asset_number
FROM wip_eam_work_requests wewr
WHERE wewr.work_request_status_id not in (5, 6)
AND wewr.maintenance_object_type = 3
AND wewr.maintenance_object_id = p_maintenance_object_id);
select network_asset_flag into l_network_asset_flag
from csi_item_instances
where instance_id = p_maintenance_object_id;
select cii.instance_number
into l_parent_asset_number
from csi_item_instances cii, wip_discrete_jobs wdj
where wdj.wip_entity_id = p_parent_job_id
and wdj.organization_id = p_organization_id
and wdj.maintenance_object_type = 3
and wdj.maintenance_object_id = cii.instance_id;
,P_UPDATE_HIERARCHY IN VARCHAR2
,P_START IN DATE
,P_END IN DATE
,P_DELTA IN NUMBER
,X_RETURN_STATUS OUT NOCOPY VARCHAR2
,X_MSG_COUNT OUT NOCOPY NUMBER
,X_MSG_DATA OUT NOCOPY VARCHAR2
)
is
l_stmt_num number := 0;
if (nvl(p_update_hierarchy, 'N') = 'N') then
if (p_operation_seq_num is null AND p_resource_seq_num is null) then
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = p_operation_seq_num;
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id in (select p_wip_entity_id from dual
union
select child_object_id from wip_sched_relationships
where relationship_type = 1
start with parent_object_id = p_wip_entity_id
connect by prior child_object_id = parent_object_id )
and organization_id = p_organization_id;
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id in (select p_wip_entity_id from dual
union
select child_object_id from wip_sched_relationships
where relationship_type = 1
start with parent_object_id = p_wip_entity_id
connect by prior child_object_id = parent_object_id )
and organization_id = p_organization_id;
update wip_operation_resource_usage
set start_date = decode(p_delta, null, p_start, start_date + p_delta),
completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
where wip_entity_id in (select p_wip_entity_id from dual
union
select child_object_id from wip_sched_relationships
where relationship_type = 1
start with parent_object_id = p_wip_entity_id
connect by prior child_object_id = parent_object_id )
and organization_id = p_organization_id;
,P_UPDATE_HIERARCHY IN VARCHAR2
,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) := 'adjust_woru';
select woru.start_date, woru.completion_date, woru.instance_id
from wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id;
P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
P_START => null,
P_END => null,
P_DELTA => P_DELTA,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
select min(WORU.start_date), max(WORU.completion_date)
into l_min_woru_start_date, l_max_woru_end_date
from wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id;
select wor.start_date, wor.completion_date
into l_wor_start_date, l_wor_end_date
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = p_resource_seq_num
and wor.organization_id = p_organization_id;
--Update the rows in WORU where instance_id is null
update wip_operation_resource_usage woru
set start_date = l_wor_start_date
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id
and WORU.start_date = l_min_woru_start_date
and woru.instance_id is null;
--Update the rows in WORU where instance_id is null
update wip_operation_resource_usage woru
set completion_date = l_wor_end_date
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id
and WORU.completion_date = l_max_woru_end_date
and woru.instance_id is null;
/*Added # 6159641, to update woru correctly when resource rescheduled by moving the bar with out
changing duration, query woru again to get the recently updated data*/
select min(WORU.start_date), max(WORU.completion_date)
into l_min_woru_start_date, l_max_woru_end_date
from wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id;
select count(*)
into l_instance_count
from wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num
and woru.organization_id = p_organization_id
and (woru.instance_id is not null
or woru.serial_number is not null);
P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
P_START => l_start_date,
P_END => l_end_date,
P_DELTA => null,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
SELECT el.location_codes
FROM eam_org_maint_defaults eomd,
mtl_eam_locations el
WHERE eomd.area_id = el.location_id
AND eomd.object_type = 50
AND eomd.object_id = p_instance_id
AND eomd.organization_id = p_maint_org_id;
select active_start_date,active_end_date
into l_active_start_date,l_active_end_date
from csi_item_instances
where instance_id = p_instance_id;
SELECT organization_id,
maintenance_object_type,
maintenance_object_id
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id;
select count(1) into l_obj_exists
from mtl_system_items_b_kfv msik
where msik.inventory_item_id = l_maint_object_id
AND msik.organization_id = l_org_id;
select count(1) into l_obj_exists
from csi_item_instances cii,
mtl_serial_numbers msn
where cii.serial_number = msn.serial_number
and cii.inventory_item_id = msn.inventory_item_id
and cii.instance_id = l_maint_object_id
and cii.last_vld_organization_id = l_org_id
and msn.current_status = 4
and nvl(cii.network_asset_flag,'N') <> 'Y';
PROCEDURE update_logical_asset(
p_inventory_item_id number
,p_serial_number varchar2
,p_equipment_gen_object_id number
,p_network_asset_flag varchar2
,p_pn_location_id number
,x_return_status out nocopy varchar2
) is
l_gen_object_id number;
select msn.gen_object_id
into l_gen_object_id
from mtl_serial_numbers msn
where msn.serial_number = p_serial_number
and msn.inventory_item_id = p_inventory_item_id
;
update mtl_serial_numbers
set group_mark_id = 1
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
update mtl_serial_numbers
set eam_linear_location_id = -1
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
end update_logical_asset;
SELECT MIN(scheduled_start_date)
INTO l_scheduled_start_date
FROM (
select scheduled_start_date
FROM WIP_DISCRETE_JOBS wdj_child
WHERE wdj_child.wip_entity_id= p_wip_entity_id
union all
SELECT scheduled_start_date
FROM WIP_DISCRETE_JOBS wdj_child
where wdj_child.wip_entity_id
IN (SELECT child_object_id
FROM eam_wo_relationships
WHERE parent_relationship_type =1
START WITH parent_object_id = p_wip_entity_id
AND parent_relationship_type = 1
CONNECT BY parent_object_id = prior child_object_id
AND parent_relationship_type = 1 ) ) ;
SELECT MAX(scheduled_completion_date)
INTO l_scheduled_completion_date
FROM (
SELECT scheduled_completion_date
FROM WIP_DISCRETE_JOBS wdj_child
WHERE wdj_child.wip_entity_id=p_wip_entity_id
union all
Select Scheduled_completion_date
from WIP_DISCRETE_JOBS wdj_child
where wdj_child.wip_entity_id
IN (SELECT child_object_id
FROM eam_wo_relationships
WHERE parent_relationship_type =1
START WITH parent_object_id = p_wip_entity_id
AND parent_relationship_type = 1
CONNECT BY parent_object_id = prior child_object_id
AND parent_relationship_type = 1));