[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(SUM(1*24),0) AS hours_24
FROM bom_calendar_dates
WHERE calendar_code = p_calendar_code
AND calendar_date BETWEEN l_horizon_start_date AND l_horizon_end_date
AND seq_num IS NOT NULL ;
SELECT bdr2.available_24_hours_flag AS available_24
FROM bom_department_resources bdr2
WHERE bdr2.department_id = p_dept_id
AND bdr2.resource_id = p_resource_id ;
SELECT count(1) INTO l_count_competence
FROM PER_COMPETENCE_ELEMENTS pce
WHERE pce.person_id = p_person_id
AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND pce.type = 'PERSONAL'
AND pce.competence_id = p_competence_id
AND ( pce.effective_date_from IS NULL
OR trunc(sysdate) >= pce.effective_date_from
)
AND ( pce.effective_date_to IS NULL
OR trunc(sysdate) < pce.effective_date_to
);
SELECT COUNT(1) INTO l_count_competence_type
FROM PER_COMPETENCE_ELEMENTS pce,
PER_COMPETENCE_ELEMENTS pce1
WHERE pce.person_id = p_person_id
AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND pce.type = 'PERSONAL'
AND pce.competence_id = pce1.competence_id
AND (pce.effective_date_from IS NULL
OR trunc(sysdate) >= pce.effective_date_from
)
AND (pce.effective_date_to IS NULL
OR trunc(sysdate) < pce.effective_date_to
)
AND pce1.business_group_id = pce.business_group_id
AND pce1.type = 'COMPETENCE_USAGE'
AND pce1.competence_type = p_competence_type
AND (pce1.effective_date_from IS NULL
OR trunc(sysdate) >= pce1.effective_date_from
)
AND (pce1.effective_date_to IS NULL
OR trunc(sysdate) < pce1.effective_date_to
);
SELECT DISTINCT
ppf.person_id,
ppf.full_name,
ppf.employee_number
FROM bom_departments bd,
bom_department_resources bdr,
bom_dept_res_instances bdri,
bom_resource_employees bre,
per_people_f ppf
WHERE bd.organization_id = p_organization_id
AND ( bd.disable_date IS NULL
OR (bd.disable_date > sysdate))
AND (p_department_id IS NULL
OR bd.department_id = p_department_id)
AND bdr.department_id = bd.department_id
AND (p_resource_id IS NULL
OR bdr.resource_id = p_resource_id)
AND bdri.resource_id = bdr.resource_id
AND (bdri.department_id = bdr.department_id OR bdri.department_id = bdr.share_from_dept_id )
AND bdri.serial_number is null
AND bdri.instance_id = bre.instance_id
AND bre.organization_id = bd.organization_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date > sysdate
AND (p_person_id IS NULL
OR bre.person_id = p_person_id)
AND bre.person_id = ppf.person_id
AND ppf.effective_start_date <= sysdate
AND ppf.effective_end_date > sysdate
AND ppf.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND ( ppf.current_employee_flag is null
OR ppf.current_employee_flag = 'Y' )
AND ( p_competence_id IS NULL OR p_competence_id IN
( SELECT competence_id
FROM PER_COMPETENCE_ELEMENTS pce
WHERE pce.person_id = ppf.person_id
AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND pce.type = 'PERSONAL'
AND pce.competence_id = p_competence_id
AND ( pce.effective_date_from IS NULL OR trunc(sysdate) >= pce.effective_date_from )
AND ( pce.effective_date_to IS NULL OR trunc(sysdate) < pce.effective_date_to)
)
)
AND ( p_competence_type IS NULL OR p_competence_type IN
( SELECT pce1.competence_type
FROM PER_COMPETENCE_ELEMENTS pce, PER_COMPETENCE_ELEMENTS pce1
WHERE pce.person_id = ppf.person_id
AND pce.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND pce.type = 'PERSONAL'
AND pce.competence_id = pce1.competence_id
AND (pce.effective_date_from IS NULL OR trunc(sysdate) >= pce.effective_date_from )
AND (pce.effective_date_to IS NULL OR trunc(sysdate) < pce.effective_date_to )
AND pce1.business_group_id = pce.business_group_id
AND pce1.type = 'COMPETENCE_USAGE'
AND pce1.competence_type = p_competence_type
AND (pce1.effective_date_from IS NULL OR trunc(sysdate) >= pce1.effective_date_from )
AND (pce1.effective_date_to IS NULL OR trunc(sysdate) < pce1.effective_date_to )
)
) ;
SELECT bre2.instance_id
FROM bom_resource_employees bre2
WHERE bre2.person_id = p_person_id
AND bre2.organization_id = p_organization_id
AND bre2.effective_start_date <= sysdate
AND bre2.effective_end_date > sysdate
AND (p_resource_id IS NULL
OR bre2.resource_id = p_resource_id);
SELECT bdr.department_id,
bdr.resource_id
FROM bom_dept_res_instances bdri2 , bom_department_resources bdr
WHERE bdri2.instance_id = p_inst_id
AND bdri2.resource_id = bdr.resource_id
AND ( p_department_id IS NULL OR bdr.department_id = p_department_id )
AND ( p_department_id IS NULL OR bdri2.department_id = p_department_id OR bdri2.department_id = bdr.share_from_dept_id ) ;
SELECT br.resource_code,
br.unit_of_measure as uom_code
FROM bom_resources br
WHERE br.resource_id = p_resource_id;
SELECT bd.department_code
FROM bom_departments bd
WHERE bd.department_id = p_department_id;
SELECT woru.wip_entity_id wip_entity_id,
woru.completion_date wo_end_dt,
woru.start_date wo_st_dt,
(
CASE
WHEN (p_horizon_end_date> woru.completion_date) THEN
woru.completion_date
ELSE
p_horizon_end_date
END
) AS task_bar_completion_date,
(
CASE
WHEN (p_horizon_start_date> woru.start_date) THEN
woru.start_date
ELSE
p_horizon_start_date
END
) AS task_bar_start_date,
(
SELECT wip_entity_name
FROM wip_entities we
WHERE we.wip_entity_id = woru.wip_entity_id
AND we.organization_id = woru.organization_id
) AS WorkOrderName,
(
SELECT br.resource_code
FROM bom_resource_employees bre,
bom_resources br
WHERE bre.instance_id = woru.instance_id
AND bre.organization_id = woru.organization_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date > sysdate
AND br.resource_id = bre.resource_id
AND br.organization_id = woru.organization_id
AND ( br.disable_date IS NULL
OR br.disable_date > sysdate)
) AS Resource_code,
( DECODE(woru.organization_id,p_organization_id,
( CASE WHEN (wdj.status_type IN (5,7,12)) THEN
'Disable'
ELSE
'Enable'
END
),'Disable')
) AS Enable_Row_Switcher ,
(
SELECT ROUND(SUM(wor.usage_rate_or_amount),2)
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = woru.wip_entity_id
AND wor.organization_id = woru.organization_id
AND wor.operation_seq_num = woru.operation_seq_num
AND wor.resource_seq_num = woru.resource_seq_num
) AS usage ,
woru.operation_seq_num,
woru.resource_seq_num
FROM wip_operation_resource_usage woru ,
wip_discrete_jobs wdj,
bom_resource_employees bre
WHERE bre.person_id = l_person_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date > sysdate
AND woru.instance_id = bre.instance_id
AND wdj.wip_entity_id = woru.wip_entity_id
AND wdj.organization_id = woru.organization_id
AND woru.instance_id IS NOT NULL;
SELECT
wo.first_unit_start_date as start_date,
wo.last_unit_completion_date as completion_date,
ROUND(((wo.last_unit_completion_date - wo.first_unit_start_date)*24),2) as duration,
wo.department_id as context_dept_id
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wo.operation_seq_num = p_operation_seq_num
AND wo.repetitive_schedule_id is null ;
SELECT woru.instance_id
FROM wip_operation_resource_usage woru ,
bom_departments bd
WHERE woru.wip_entity_id = p_wip_entity_id
AND woru.organization_id = p_organization_id
AND woru.operation_seq_num = p_operation_seq_num
AND woru.instance_id = l_instance_id;
SELECT
wor.start_date as start_date,
wor.completion_date as completion_date,
ROUND(((wor.completion_date - wor.start_date)*24),2) as duration,
wor.resource_id as context_res_id
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
AND wor.REPETITIVE_SCHEDULE_ID is null ;
SELECT calendar_code INTO l_calendar_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT wdj.FIRM_PLANNED_FLAG INTO l_firm_status
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id ;
--insert the values into the record EmpSearchRslt_Rec
--leave the assigned hour,unassigned hour,available hour and percentage assigned as null.
l_duration := ROUND( (l_emp_end_date - l_emp_st_date )*24,2 ) ;
--insert the record into table after initialization
l_Emp_Search_Result_Tbl.EXTEND;
DELETE FROM Eam_Emp_Search_Result_Tbl;
INSERT INTO Eam_Emp_Search_Result_Tbl
VALUES l_Emp_Search_Result_Tbl(indx);
DELETE FROM Eam_Emp_Assignment_Details_Tbl ;
using INSERT with SELECT
*/
INSERT INTO Eam_Emp_Assignment_Details_Tbl (
wip_entity_id,
wo_end_dt,
wo_st_dt ,
workordername ,
resource_code ,
update_switcher ,
usage ,
operation_seq_num ,
resource_seq_num ,
person_id ,
wo_assign_check ,
assign_switcher ,
instance_id ,
organization_id ,
employee_name ,
firm_status )
SELECT woru.wip_entity_id wip_entity_id,
woru.completion_date wo_end_dt,
woru.start_date wo_st_dt,
(
SELECT wip_entity_name
FROM wip_entities we
WHERE we.wip_entity_id = woru.wip_entity_id
AND we.organization_id = woru.organization_id
) AS WorkOrderName,
(
SELECT br.resource_code
FROM bom_resource_employees bre,
bom_resources br
WHERE bre.instance_id = woru.instance_id
AND bre.organization_id = woru.organization_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date > sysdate
AND br.resource_id = bre.resource_id
AND br.organization_id = woru.organization_id
AND ( br.disable_date IS NULL
OR br.disable_date > sysdate)
) AS Resource_code,
( DECODE(woru.organization_id,p_organization_id,
( CASE WHEN (wdj.status_type IN (5,7,12)) THEN
'DisableWOUpdate'
ELSE
'EnableWOUpdate'
END
),'DisableWOUpdate')
) AS Update_Switcher ,
(
SELECT ROUND(SUM(wor.usage_rate_or_amount),2)
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = woru.wip_entity_id
AND wor.organization_id = woru.organization_id
AND wor.operation_seq_num = woru.operation_seq_num
AND wor.resource_seq_num = woru.resource_seq_num
) AS usage ,
woru.operation_seq_num,
woru.resource_seq_num,
ppf.person_id as person_id,
'Y' as wo_assign_check,
( DECODE(woru.organization_id,p_organization_id,
( CASE WHEN (wdj.status_type IN ( 5,7,12,14,15 ) OR ewod.pending_flag = 'Y' ) THEN
'DisableAssign'
ELSE
'EnableAssign'
END
),'DisableAssign')
) AS Assign_Switcher,
woru.instance_id,
woru.organization_id,
ppf.full_name as employee_name ,
wdj.firm_planned_flag as firm_status
FROM wip_operation_resource_usage woru ,
wip_discrete_jobs wdj,
eam_work_order_details ewod,
bom_resource_employees bre,
per_people_f ppf
WHERE ppf.person_id = p_person_id
AND ppf.business_group_id = HR_GENERAL.GET_BUSINESS_GROUP_ID
AND ppf.EFFECTIVE_START_DATE <= sysdate
AND ppf.EFFECTIVE_END_DATE > sysdate
AND bre.person_id = ppf.person_id
AND bre.effective_start_date <= sysdate
AND bre.effective_end_date > sysdate
AND woru.instance_id = bre.instance_id
AND wdj.wip_entity_id = woru.wip_entity_id
AND wdj.organization_id = woru.organization_id
AND wdj.wip_entity_id = ewod.wip_entity_id
AND wdj.organization_id = ewod.organization_id
AND woru.start_date <= p_horizon_end_date
AND woru.completion_date >= p_horizon_start_date
AND woru.instance_id is not null ;
SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND (wo.disable_date is null OR wo.disable_date > sysdate )
AND wo.repetitive_schedule_id IS NULL;
SELECT wor.resource_seq_num , wor.usage_rate_or_amount
FROM wip_operation_resources wor, bom_resources br
WHERE wor.wip_entity_id = p_wip_entity_id
AND wor.operation_seq_num = p_op_seq_num
AND wor.ORGANIZATION_ID = p_organization_id
AND wor.repetitive_schedule_id IS NULL
AND br.resource_id = wor.resource_id
AND br.resource_type = 2 ;
SELECT ROUND(NVL((woru.completion_date - woru.start_date)*24,0),2) as assigned_hours
FROM wip_operation_resource_usage woru
WHERE woru.serial_number IS NULL
AND woru.instance_id IS NOT NULL
AND woru.wip_entity_id = p_wip_entity_id
AND woru.operation_seq_num = p_op_seq_num
AND woru.ORGANIZATION_ID = p_organization_id
AND woru.repetitive_schedule_id IS NULL
AND woru.resource_seq_num = p_res_seq_num
UNION
SELECT ROUND(NVL((wori.completion_date - wori.start_date)*24,0),2) as assigned_hours
FROM wip_op_resource_instances wori
WHERE wori.serial_number IS NULL
AND wori.instance_id IS NOT NULL
AND wori.wip_entity_id = p_wip_entity_id
AND wori.operation_seq_num = p_op_seq_num
AND wori.ORGANIZATION_ID = p_organization_id
AND wori.resource_seq_num = p_res_seq_num
AND NOT EXISTS (SELECT 1
FROM wip_operation_resource_usage woru1
WHERE woru1.serial_number IS NULL
AND woru1.instance_id = wori.instance_id
AND woru1.wip_entity_id = wori.wip_entity_id
AND woru1.operation_seq_num = wori.operation_seq_num
AND woru1.ORGANIZATION_ID = wori.ORGANIZATION_ID
AND woru1.resource_seq_num = wori.resource_seq_num);
SELECT 1
FROM bom_calendar_dates bcd
WHERE bcd.calendar_code = p_calendar_code
AND calendar_date = p_date
AND seq_num IS NOT NULL ;
SELECT
bst.from_time,bst.to_time
FROM bom_resource_shifts brs,
bom_shift_dates bsd,
bom_shift_times bst
WHERE brs.department_id = p_dept_id
AND brs.resource_id = p_resource_id
AND brs.shift_num = bsd.shift_num
AND bsd.seq_num is not null
AND bsd.calendar_code = p_calendar_code
AND bsd.shift_date = p_date
AND bst.calendar_code = bsd.calendar_code
AND bst.shift_num = bsd.shift_num;
SELECT
bst.from_time,bst.to_time
FROM bom_resource_shifts brs,
bom_shift_dates bsd,
bom_shift_times bst
WHERE brs.department_id = p_dept_id
AND brs.resource_id = p_resource_id
AND brs.shift_num = bsd.shift_num
AND bsd.seq_num is not null
AND bsd.calendar_code = p_calendar_code
AND bsd.shift_date = p_date
AND bst.calendar_code = bsd.calendar_code
AND bst.shift_num = bsd.shift_num;
SELECT
bst.from_time,bst.to_time
FROM bom_resource_shifts brs,
bom_shift_dates bsd,
bom_shift_times bst
WHERE brs.department_id = p_dept_id
AND brs.resource_id = p_resource_id
AND brs.shift_num = bsd.shift_num
AND bsd.seq_num is not null
AND bsd.calendar_code = p_calendar_code
AND bsd.shift_date = p_date
AND bst.calendar_code = bsd.calendar_code
AND bst.shift_num = bsd.shift_num;