[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_stack => fnd_api.g_false);
select brs.shift_num,
bsd.shift_date + bst.from_time/86400,
bsd.shift_date + bst.to_time/86400
from bom_resource_shifts brs,
mtl_parameters mp,
bom_shift_dates bsd,
bom_shift_times bst,
bom_department_resources bdr
where bdr.department_id = v_deptID
and bdr.resource_id = v_resID
and brs.resource_id = bdr.resource_id
and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
and mp.organization_id = v_orgID
and mp.calendar_code = bsd.calendar_code
and mp.calendar_exception_set_id = bsd.exception_set_id
and brs.shift_num = bsd.shift_num
and bsd.shift_date between v_startDate and v_endDate --don't incorporate time into this check as it slows the query
and bsd.seq_num is not null
and bst.shift_num = bsd.shift_num
and bst.calendar_code = bsd.calendar_code
order by bsd.shift_date, bst.from_time;
select -1 shiftNum,
bcd.calendar_date + wl.start_time/86400,
bcd.calendar_date + wl.stop_time/86400
from mtl_parameters mp,
bom_calendar_dates bcd,
wip_lines wl
where mp.organization_id = v_orgID
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and wl.line_id = v_repLineID
and bcd.seq_num is not null --working day
and bcd.calendar_date between v_startDate and v_endDate
order by bcd.calendar_date;
select -1,
bcd.calendar_date,
bcd.calendar_date + 1
from mtl_parameters mp,
bom_calendar_dates bcd
where mp.organization_id = v_orgID
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date between v_startDate and v_endDate
and bcd.seq_num is not null
order by bcd.calendar_date;
select 1
into l_dummy
from dual
where exists (select 1
from bom_resource_shifts brs,
mtl_parameters mp,
bom_shift_dates bsd,
bom_shift_times bst,
bom_department_resources bdr
where bdr.department_id = x_resTbls.deptID(p_idx)
and bdr.resource_id = x_resTbls.resID(p_idx)
and brs.resource_id = bdr.resource_id
and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
and mp.organization_id = p_orgID
and mp.calendar_code = bsd.calendar_code
and mp.calendar_exception_set_id = bsd.exception_set_id
and brs.shift_num = bsd.shift_num
and bsd.seq_num is not null
and bst.shift_num = bsd.shift_num
and bst.calendar_code = bsd.calendar_code);
It will change the resource shifts (add day, delete day, modify shift times) as per capacity
exceptions.
*/
procedure capacityExceptions(p_resID IN NUMBER,
p_deptID IN NUMBER,
p_orgID IN NUMBER,
x_shifts IN OUT NOCOPY shift_recTbl_t,
x_returnStatus OUT NOCOPY VARCHAR2) is
cursor c_capacityDtls(v_resID NUMBER,
v_deptID NUMBER,
v_orgID NUMBER) is
select shift_num,
from_date,
to_date,
from_time,
to_time,
capacity_change,
action_type
from bom_resource_changes brc,
crp_simulation_sets crp
where department_id = v_deptID
and resource_id = v_resID
and organization_id = v_orgID
and crp.simulation_set = brc.simulation_set
and crp.use_in_wip_flag = 1;
if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
WHILE (j <= l_lastRow) LOOP
IF ( Trunc(l_capacity(i).fromDate) = Trunc(x_shifts.startDate(j)) ) then
l_currRow := j; -- row that needs to be deleted
l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
select bc.calendar_end_date
into l_maxDate
from bom_calendars bc, mtl_parameters mp
where mp.organization_id = p_orgID
and mp.calendar_code = bc.calendar_code;
select brs.shift_num shiftNum,
bsd.shift_date + bst.from_time/86400,
bsd.shift_date + bst.to_time/86400
from bom_resource_shifts brs,
mtl_parameters mp,
bom_shift_dates bsd,
bom_shift_times bst,
bom_department_resources bdr
where bdr.department_id = v_deptID
and bdr.resource_id = v_resID
and brs.resource_id = bdr.resource_id
and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
and mp.organization_id = v_orgID
and mp.calendar_code = bsd.calendar_code
and mp.calendar_exception_set_id = bsd.exception_set_id
and brs.shift_num = bsd.shift_num
and bsd.shift_date between v_startDate and v_endDate --don't incorporate time into this check as it slows the query
and bsd.seq_num is not null
and bst.shift_num = bsd.shift_num
and bst.calendar_code = bsd.calendar_code
order by bsd.shift_date desc, bst.from_time desc;
select -1,
bcd.calendar_date,
bcd.calendar_date + 1
from mtl_parameters mp,
bom_calendar_dates bcd
where mp.organization_id = v_orgID
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date between v_startDate and v_endDate
and bcd.seq_num is not null
order by bcd.calendar_date desc;
select -1 shiftNum,
bcd.calendar_date + wl.start_time/86400,
bcd.calendar_date + wl.stop_time/86400
from mtl_parameters mp,
bom_calendar_dates bcd,
wip_lines wl
where mp.organization_id = v_orgID
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and wl.line_id = v_repLineID
and bcd.seq_num is not null --working day
and bcd.calendar_date between v_startDate and v_endDate --use stop_time to comsume tail end of a shift
order by bcd.calendar_date desc;
select 1
into l_dummy
from dual
where exists (select 1
from bom_resource_shifts brs,
mtl_parameters mp,
bom_shift_dates bsd,
bom_shift_times bst,
bom_department_resources bdr
where bdr.department_id = x_resTbls.deptID(p_idx)
and bdr.resource_id = x_resTbls.resID(p_idx)
and brs.resource_id = bdr.resource_id
and brs.department_id = nvl(bdr.share_from_dept_id,bdr.department_id)
and mp.organization_id = p_orgID
and mp.calendar_code = bsd.calendar_code
and mp.calendar_exception_set_id = bsd.exception_set_id
and brs.shift_num = bsd.shift_num
and bsd.seq_num is not null
and bst.shift_num = bsd.shift_num
and bst.calendar_code = bsd.calendar_code);
select bc.calendar_start_date
into l_minDate
from bom_calendars bc, mtl_parameters mp
where mp.organization_id = p_orgID
and mp.calendar_code = bc.calendar_code;
x_resTbls.usgStartDate.delete;
x_resTbls.usgEndDate.delete;
x_resTbls.usgCumMinProcTime.delete;
x_resTbls.usgStartIdx.delete;
x_resTbls.usgEndIdx.delete;
x_resTbls.startDate.delete;
x_resTbls.endDate.delete;
x_resTbls.usgStartDate.delete;
x_resTbls.usgEndDate.delete;
x_resTbls.usgCumMinProcTime.delete;
x_resTbls.usgStartIdx.delete;
x_resTbls.usgEndIdx.delete;
x_resTbls.startDate.delete;
x_resTbls.endDate.delete;
select resource_code
into l_resCode
from bom_resources
where resource_id = p_resTbls.resID(i);