[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_stack => fnd_api.g_false);
Will delete the -1 shifts together later, otherwise with everyloop
there will be sorting of the records required, which will have
performance impact.*/
x_shifts.shiftNum(i) := -1;
Will delete the -1 shifts together later, otherwise with everyloop
there will be sorting of the records required, which will have
performance impact.*/
x_shifts.shiftNum(i) := -1;
/*Looping in x_shifts to delete overlapping shifts and to create one most inclusive shifts.
Since shifts are getting merged thus Shift Numbers may be incorrect after this processing.
But in later part of the code, Shift Numbers are not needed and only start and end times
are used.*/
while(l_overlap_exists) loop
if (l_logLevel <= wip_constants.trace_logging) then
wip_logger.log('while loop begins', l_retStatus);
wip_logger.log('deleted counter: ' || i, l_retStatus);
/*Trim the remaining shifts. These shifts are deleted above and have shiftNum as -1*/
x_shifts.shiftNum.trim(l_count-l_count2);
select brs.shift_num,
bsd.shift_date + bst.from_time/86400,
--added the below case statement for bug 16481026.
-- This fix is to consider the resource scheduled from 00:00:00 to 00:00:00 properly
-- to ensure that the start time and end time for such resource are not same.
CASE WHEN (bst.to_time = bst.from_time or bst.to_time < bst.from_time)
THEN bsd.shift_date + bst.to_time/86400 + 1
ELSE
bsd.shift_date + bst.to_time/86400
END
--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,
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 brs.shift_num is not null
and rownum = 1);
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
/*bug 10251978 (FP 10086620): Modified the cursor to include capacity_units value from bom_resource_shifts table*/
cursor c_capacityDtls(v_resID NUMBER,
v_deptID NUMBER,
v_orgID NUMBER) is
select brc.shift_num,
brc.from_date,
brc.to_date,
brc.from_time,
brc.to_time,
brc.capacity_change,
brc.action_type,
brs.capacity_units
from bom_resource_changes brc,
crp_simulation_sets crp,
BOM_RESOURCE_SHIFTS brs
where brc.department_id = v_deptID
and brc.resource_id = v_resID
and crp.organization_id = v_orgID
and crp.simulation_set = brc.simulation_set
and crp.use_in_wip_flag = 1
and brs.department_id = v_deptID
and brs.resource_id = v_resID
and brs.shift_num = brc.shift_num;
This will avoid shift being deleted if customer define shift start time and endtime the same as 00:00:00*/
if(l_capacity.count = 0) then
if(l_logLevel <= wip_constants.trace_logging) then
wip_logger.log('No Capacity Exception found. exiting capacity exception', l_retStatus);
l_capacity.DELETE;
l_capacity_2.DELETE;
if (l_capacity(i).actionType = wip_constants.DELETE_WKDY) THEN -- delete a working day
/* If more than one shift exists for a day (in case of shift with breakups) then it will
delete all those shifts for that day. */
if(l_logLevel <= wip_constants.trace_logging) then
wip_logger.log('inside delete a working day', l_retStatus);
/* Changed IF condition to WHILE loop to delete all the shifts for the given day */
WHILE ( j <= l_lastRow AND Trunc(l_capacity(i).fromDate) = Trunc(x_shifts.startDate(j)) ) LOOP
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
x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
j := x_shifts.shiftNum.PRIOR(j); -- update j as one shift record is deleted
x_shifts.shiftNum.extend; -- extend the xshifts table by one row and insert the day and then shift remaning days
l_lastRow := x_shifts.shiftNum.LAST; -- updated last row
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,
--added the below case statement for bug 16481026.
-- This fix is to consider the resource scheduled from 00:00:00 to 00:00:00 properly
-- to ensure that the start time and end time for such resource are not same.
CASE WHEN (bst.to_time = bst.from_time or bst.to_time < bst.from_time)
THEN bsd.shift_date + bst.to_time/86400 + 1
ELSE
bsd.shift_date + bst.to_time/86400
END
--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,
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 brs.shift_num is not null
and rownum = 1);
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);