[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mp.calendar_code
into l_calendar_code
from mtl_parameters mp
where mp.organization_id = p_organization_id;
select distinct muc1.uom_class
from mtl_uom_conversions muc1,
mtl_uom_conversions muc2
where (muc1.uom_class = muc2.uom_class
and nvl(muc1.disable_date, sysdate + 1) > sysdate)
and nvl(muc2.disable_date, sysdate + 1) > sysdate
and muc1.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE')
and muc2.uom_code = p_uom_code;
select
max(bsd.shift_date + st.to_time/(60*60*24))
into l_end_time
from
bom_shift_dates bsd,
(select bst.shift_num,
min(bst.from_time) from_time,
max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
from bom_shift_times bst
where bst.calendar_code = p_calendar_code
group by bst.shift_num) st
where bsd.calendar_code = p_calendar_code
and bsd.shift_num = st.shift_num
and bsd.exception_set_id = -1
and bsd.seq_num is not null
and bsd.shift_date = trunc(p_date)
and bsd.shift_date + st.to_time/(60*60*24) <= l_temp_time
and bsd.shift_date + st.from_time/(60*60*24) < l_temp_time;
select
max(bsd.shift_date + st.to_time/(60*60*24))
into l_end_time
from
bom_shift_dates bsd,
(select bst.shift_num,
max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
from bom_shift_times bst
where bst.calendar_code = p_calendar_code
group by bst.shift_num) st
where bsd.calendar_code = p_calendar_code
and bsd.shift_num = st.shift_num
and bsd.exception_set_id = -1
and bsd.seq_num is not null
and bsd.shift_date = trunc(l_temp_time);
select shift_date
into l_eff_date
from bom_shift_dates
where calendar_code = l_calendar_code
and exception_set_id = -1
and seq_num = p_seq_num
and shift_num =p_shift_num;
select shift_date
into l_eff_date
from bom_shift_dates
where calendar_code = l_calendar_code
and exception_set_id = -1
and seq_num = p_seq_num
and shift_num =p_shift_num;
select bre.organization_id,
bdri.department_id,
bdri.resource_id,
bdri.instance_id,
bre.person_id
from per_all_people_f papf,
bom_resource_employees bre,
bom_dept_res_instances bdri,
bom_department_resources bdr,
bom_resources br
where papf.person_id = bre.person_id
and bre.instance_id = bdri.instance_id
and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
and bre.resource_id = bdri.resource_id
and bdri.department_id = bdr.department_id
and bdri.resource_id = bdr.resource_id
and bdr.resource_id = br.resource_id
and bdr.available_24_hours_flag = 2
and wip_ws_labor_metric_pub.is_time_uom(br.unit_of_measure) = 1
and bre.organization_id = org_id
order by bdri.department_id,
bdri.resource_id;
select attribute_name,
substr(attribute_name,2,1) as identifier,
attribute_value_code
from wip_preference_values
where preference_id = 35
and level_id = 1
and sequence_number = (select sequence_number
from wip_preference_values
where preference_id = 35
and level_id = 1
and attribute_name = 'Org'
and attribute_value_code = to_char(org_id))
order by 1 desc;
select trunc(bsd.seq_num) shift_date_seq,
bsd.shift_num shift_num,
bsd.shift_date + st.from_time/(60*60*24) shift_start_time,
bsd.shift_date + st.to_time/(60*60*24) shift_end_time,
(bsd.shift_date + st.to_time/(60*60))-(bsd.shift_date + st.from_time/(60*60)) shift_duration
from bom_shift_dates bsd,
(select bst.shift_num,
min(bst.from_time) from_time,
max(bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0)) to_time
from bom_shift_times bst
where bst.calendar_code = p_calendar_code
group by bst.shift_num) st,
bom_calendar_shifts bcs,
bom_resource_shifts brs
where bsd.calendar_code = p_calendar_code
and bsd.exception_set_id = -1
and bsd.shift_date between trunc(start_period) and trunc(end_period)
and bsd.shift_num = st.shift_num
and bsd.seq_num is not null
and bsd.calendar_code = bcs.calendar_code
and bsd.shift_num = bcs.shift_num
and fnd_date.canonical_to_date(to_char((bsd.shift_date + st.to_time/(60*60*24)),WIP_CONSTANTS.DATETIME_FMT)) < end_period
and brs.shift_num= bcs.shift_num
and brs.department_id = dept_id
and brs.resource_id = res_id;
select start_date as actual_date,
decode(action_flag,2,-duration,duration) as duration,
uom_code
from wip_resource_actual_times
where organization_id = org_id
and employee_id = emp_id
and wip_entity_id is null
and time_entry_mode in (6,8)
and start_date between start_time and end_time
and duration is not null;
select start_date as actual_date,
decode(action_flag,2,-duration,duration) as duration,
uom_code
from wip_resource_actual_times
where organization_id = org_id
and employee_id = emp_id
and wip_entity_id is null
and time_entry_mode =7
and start_date between start_time and end_time
and duration is not null;
select wrat.start_date as actual_date,
decode(wrat.action_flag,2,-wrat.duration,wrat.duration) as duration,
wrat.uom_code
from wip_resource_actual_times wrat,
wip_operations wo,
wip_entities we
where wrat.organization_id = org_id
and wrat.organization_id= wo.organization_id
and wrat.wip_entity_id = wo.wip_entity_id
and we.organization_id = wo.organization_id
and we.wip_entity_id = wo.wip_entity_id
and we.entity_type=1
and wrat.operation_seq_num = wo.operation_seq_num
and wo.department_id = dept_id
and wrat.resource_id = res_id
and wrat.employee_id = emp_id
and wrat.wip_entity_id is not null
and wrat.duration is not null
and wrat.start_date between start_time and end_time;
select distinct wrat.wip_entity_id as wip_entity_id,
wrat.operation_seq_num as operation_seq_num,
get_index_for_date(wrat.start_date,wrat.organization_id,wo.department_id,res_id) as emp_index
from wip_resource_actual_times wrat,
wip_operations wo,
wip_entities we
where wrat.organization_id = org_id
and wrat.organization_id= wo.organization_id
and wrat.wip_entity_id = wo.wip_entity_id
and we.organization_id = wo.organization_id
and we.wip_entity_id = wo.wip_entity_id
and we.entity_type=1
and wrat.operation_seq_num = wo.operation_seq_num
and wo.department_id = dept_id
and wrat.resource_id = res_id
and wrat.employee_id = emp_id
and wrat.wip_entity_id is not null
and wrat.duration is not null
and wrat.start_date between start_time and end_time;
select sum(nvl(t.move_qty,0)) as moved_qty
from
(select
wo.wip_entity_id as job,
wo.operation_seq_num as op,
fm_operation_seq_num as fm_op,
fm_intraoperation_step_type as fm_step,
to_operation_seq_num as to_op,
to_intraoperation_step_type as to_step,
decode(wmt.to_intraoperation_step_type,
1, (case when ((wmt.to_operation_seq_num > wo.operation_seq_num)) then wmt.primary_quantity
when ((wmt.to_operation_seq_num < wo.operation_seq_num)) then -1 * wmt.primary_quantity
when (wmt.to_operation_seq_num <= wmt.fm_operation_seq_num
and wmt.to_operation_seq_num = wo.operation_seq_num
and wmt.fm_intraoperation_step_type not in (4,5)) then -1 * wmt.primary_quantity
else 0 end),
2, (case when ((wmt.to_operation_seq_num > wo.operation_seq_num)) then wmt.primary_quantity
when ((wmt.to_operation_seq_num < wo.operation_seq_num)) then -1 * wmt.primary_quantity
when (wmt.to_operation_seq_num <= wmt.fm_operation_seq_num
and wmt.to_operation_seq_num = wo.operation_seq_num
and wmt.fm_intraoperation_step_type not in (4,5)) then -1 * wmt.primary_quantity
else 0 end),
3, (case when (wmt.to_operation_seq_num >= wo.operation_seq_num) then wmt.primary_quantity
when (wmt.to_operation_seq_num < wo.operation_seq_num
and (wmt.fm_intraoperation_step_type not in (4,5)
or wmt.fm_operation_seq_num <> wo.operation_seq_num)) then -1 * wmt.primary_quantity
else 0 end),
5, (case when wmt.to_operation_seq_num > wo.operation_seq_num then wmt.primary_quantity
when wmt.to_operation_seq_num < wo.operation_seq_num then -1 * wmt.primary_quantity
when wmt.to_operation_seq_num = wo.operation_seq_num then 0
else 0 end),
4, (case when wmt.to_operation_seq_num > wo.operation_seq_num then wmt.primary_quantity
when wmt.to_operation_seq_num < wo.operation_seq_num then -1 * wmt.primary_quantity
when wmt.to_operation_seq_num = wo.operation_seq_num then 0
else 0 end),
0) as move_qty,
get_index_for_date(wmt.transaction_date,wmt.organization_id,wo.department_id,res_id) as emp_index
from
wip_move_transactions wmt,
wip_operations wo,
wip_entities we
where
wo.organization_id = org_id
and wo.organization_id = wmt.organization_id
and wo.organization_id = we.organization_id
and wo.wip_entity_id = we.wip_entity_id
and we.entity_type=1
and wo.wip_entity_id = wmt.wip_entity_id
and wo.operation_seq_num = op_seq
and wo.wip_entity_id = wip_id
and wo.repetitive_schedule_id is null
and wmt.transaction_date between start_time and end_time
and ((wo.operation_seq_num >= wmt.fm_operation_seq_num + decode(sign(wmt.fm_intraoperation_step_type - 2),1,1,0)
and wo.operation_seq_num < wmt.to_operation_seq_num + decode(sign(wmt.to_intraoperation_step_type - 2),1,1,0)
and (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
or (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
and wmt.fm_intraoperation_step_type <= 2
and wmt.to_intraoperation_step_type > 2)))
or (wo.operation_seq_num < wmt.fm_operation_seq_num + decode(sign(wmt.fm_intraoperation_step_type-2),1,1,0)
and wo.operation_seq_num >= wmt.to_operation_seq_num + decode(sign(wmt.to_intraoperation_step_type-2),1,1,0)
and (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
or (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
and wmt.to_intraoperation_step_type <= 2
and wmt.fm_intraoperation_step_type > 2)))
or (wmt.fm_intraoperation_step_type in (4,5)
and wo.operation_seq_num = wmt.fm_operation_seq_num))) t
where
t.emp_index = indx;
select usage_rate_or_amount *decode(sign(move_qty),-1,0,move_qty) usage_rate,uom_code,basis_type
from wip_operation_resources
where organization_id= org_id
and wip_entity_id= we_ent_id
and operation_seq_num= op_seq_num
and resource_id = res_id;
select organization_code
into l_org_code
from mtl_parameters
where organization_id = p_organization_id;
select count(1)
into l_pgm_count
from fnd_concurrent_requests
where program_application_id = g_program_appl_id
and concurrent_program_id = g_program_id
and upper(phase_code) = 'R'
and argument1 = to_char(p_organization_id);
select sysdate
into l_current_date
from dual;
/* Delete existing records in wip_labor_performance_rates table for the org.*/
delete from wip_labor_performance_rates
where organization_id= p_organization_id;
wip_ws_util.trace_log('Deleted Existing records for this Organization.');
v_emp_perf_temp.delete;
indexed by binary_integer so that we can bulk insert in the table.
Also initialize the new collection always. */
emp_index := v_emp_perf_temp.first;
v_wip_lab_perf_rate(new_index).Last_updated_by := g_user_id;
v_wip_lab_perf_rate(new_index).Last_update_date := sysdate;
v_wip_lab_perf_rate(new_index).Last_update_login := g_user_login_id;
v_wip_lab_perf_rate(new_index).Program_update_date := sysdate;
/* Bulk Insert Records into Metrics Table */
forall cntr in v_wip_lab_perf_rate.first..v_wip_lab_perf_rate.last
INSERT into WIP_LABOR_PERFORMANCE_RATES values v_wip_lab_perf_rate(cntr);
wip_ws_util.trace_log('Insertion of all calculated records is successful.');
when summing the value in the dashboard. So we compute the org level summary and insert into metrics table.*/
select t.date_sequence,
t.shift_num,
sum(t.m1_att1),
sum(t.m1_att2),
sum(t.m2_att1),
sum(t.m2_att2),
sum(t.m3_att1),
sum(t.m3_att2)
bulk collect into
v_date_sequence,
v_shift_num,
v_m1_att1,
v_m1_att2,
v_m2_att1,
v_m2_att2,
v_m3_att1,
v_m3_att2
from (
select date_sequence,
shift_num,
decode(l_metric1_attribute1,1,max(metric1_attribute1),sum(metric1_attribute1)) m1_att1,
decode(l_metric1_attribute2,1,max(metric1_attribute2),sum(metric1_attribute2)) m1_att2,
decode(l_metric2_attribute1,1,max(metric2_attribute1),sum(metric2_attribute1)) m2_att1,
decode(l_metric2_attribute2,1,max(metric2_attribute2),sum(metric2_attribute2)) m2_att2,
decode(l_metric3_attribute1,1,max(metric3_attribute1),sum(metric3_attribute1)) m3_att1,
decode(l_metric3_attribute2,1,max(metric3_attribute2),sum(metric3_attribute2)) m3_att2
from wip_labor_performance_rates
where organization_id = p_organization_id
group by
date_sequence,
shift_num,
employee_id
)t
group by
t.date_sequence,
t.shift_num;
/* Insert into Metrics Table the org level summary data. Use -1 for dept,res and employee */
forall org_index in v_shift_num.first..v_shift_num.last
insert into wip_labor_performance_rates
(
ORGANIZATION_ID,
DEPARTMENT_ID,
RESOURCE_ID,
EMPLOYEE_ID,
DATE_SEQUENCE,
SHIFT_NUM,
METRIC1_ATTRIBUTE1,
METRIC1_ATTRIBUTE2,
METRIC2_ATTRIBUTE1,
METRIC2_ATTRIBUTE2,
METRIC3_ATTRIBUTE1,
METRIC3_ATTRIBUTE2,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER
)
values
(
p_organization_id,
-1,
-1,
-1,
v_date_sequence(org_index),
v_shift_num(org_index),
v_m1_att1(org_index),
v_m1_att2(org_index),
v_m2_att1(org_index),
v_m2_att2(org_index),
v_m3_att1(org_index),
v_m3_att2(org_index),
g_user_id,
sysdate,
g_user_id,
sysdate,
g_user_login_id,
g_program_appl_id,
sysdate,
g_program_id,
g_request_id,
1);
wip_ws_util.trace_log('Insertion of Org Level Summary Data is successful.');