[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct wdj.organization_id maint_org_id, wdj.wip_entity_id wip_entity_id,
cii.inventory_item_id asset_group_id, cii.instance_number asset_number,
decode (wdj.shutdown_type, 2, to_number(NULL), 3, to_number(NULL), wo.operation_seq_num ) op_seq,
decode (wdj.shutdown_type, 2, wdj.scheduled_start_date, 3, wdj.scheduled_start_date, wo.first_unit_start_date ) from_date,
decode (wdj.shutdown_type, 2, wdj.scheduled_completion_date, 3, wdj.scheduled_completion_date, wo.last_unit_completion_date ) to_date,
decode (wdj.shutdown_type, 2, wdj.shutdown_type, 3, wdj.shutdown_type, wo.shutdown_type ) shutdown_type,
wdj.firm_planned_flag, msn.current_organization_id prod_org_id, msn.inventory_item_id equipment_item_id,
msn.serial_number eqp_serial_number, bdri.department_id, bre.resource_id, bre.instance_id,
1 as downtime_source_code
FROM wip_discrete_jobs wdj,
wip_entities we,
mtl_serial_numbers msn,
csi_item_instances cii,
mtl_parameters mp,
bom_resource_equipments bre,
bom_dept_res_instances bdri,
wip_operations wo
WHERE
( ( p_resource_id IS NULL) OR
( p_resource_id IS NOT NULL AND bre.resource_id = p_resource_id) )
AND bre.resource_id = bdri.resource_id
AND ( ( p_department_id IS NULL) OR
( p_department_id IS NOT NULL AND bdri.department_id = p_department_id) )
AND bre.organization_id = msn.current_organization_id
AND cii.network_asset_flag = 'N'
and msn.current_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
AND bre.inventory_item_id = msn.inventory_item_id
AND bdri.serial_number = msn.serial_number
AND cii.equipment_gen_object_id is not null
AND cii.equipment_gen_object_id = msn.gen_object_id
and wdj.maintenance_object_id = cii.instance_id
AND wdj.maintenance_object_type = 3
AND wdj.organization_id = mp.maint_organization_id
AND we.organization_id = wdj.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type = 6
AND WDJ.wip_entity_id = WO.wip_entity_id(+)
AND ( ( wdj.shutdown_type in (2,3))
OR (NVL(wdj.shutdown_type,0) NOT IN (2,3) AND WO.shutdown_type IN (2,3) AND WDJ.wip_entity_id = WO.wip_entity_id )
)
AND ( ( p_include_unreleased = 1 AND wdj.status_type in (1,3))
OR ( p_include_unreleased <> 1 AND wdj.status_type = 3))
AND ( ( p_firm_order_only = 1 AND wdj.firm_planned_flag = 1)
OR (p_firm_order_only = 2) )
ORDER BY maint_org_id, asset_group_id, asset_number, from_date, wip_entity_id, op_seq;
select calendar_code, calendar_exception_set_id into l_calendar_code, l_exception_set_id
from mtl_parameters where organization_id=p_org_id;
SELECT bom_resource_downtime_group_s.nextval
INTO l_downtime_group_id
FROM DUAL;
for a_shift_time in (select from_time, to_time from bom_shift_times
where calendar_code=p_calendar_code and shift_num=p_shift_num) loop
i := i+1;
select min(from_time), max(to_time) into l_from_time, l_to_time
from bom_shift_times
where calendar_code = p_calendar_code and shift_num = p_shift_num;
select count(shift_date) into l_count
from bom_shift_dates
where
trunc(shift_date) > trunc(p_from_date)
and trunc(shift_date) < trunc(p_to_date)
and seq_num is not null
and calendar_code = p_calendar_code and shift_num = p_shift_num;
-> This procedure is called before inserting into the table BOM_RES_INSTANCE_CHANGES
-> Check for the date/time range overlap with the existing record in BOM_RES_INSTANCE_CHANGES
-> If overlapping occurs then TRUE is returned. And if there is need for updating the record
then parameter p_out_to_update is updated to true.
-> If no overlapping, then FALSE is returned.
*/
Function check_existence(p_downtime_row IN downtime_csr%ROWTYPE,
p_shift_num IN number,
p_simulation_set IN varchar2,
p_from_date IN date,
p_from_time IN number,
p_to_date IN date,
p_to_time IN number,
p_action_type IN number,
p_out_from_date OUT NOCOPY date,
p_out_from_time OUT NOCOPY number,
p_out_to_date OUT NOCOPY date,
p_out_to_time OUT NOCOPY number,
p_out_from_date_old OUT NOCOPY date,
p_out_from_time_old OUT NOCOPY number,
p_out_to_date_old OUT NOCOPY date,
p_out_to_time_old OUT NOCOPY number,
p_out_to_update OUT NOCOPY boolean) return boolean is
l_from_date_old date;
p_out_to_update := FALSE;
select from_date, from_time, to_date, to_time
into p_out_from_date_old, p_out_from_time_old, p_out_to_date_old, p_out_to_time_old
from bom_res_instance_changes
where department_id = p_downtime_row.department_id
and resource_id = p_downtime_row.resource_id
and shift_num = p_shift_num
and simulation_set = p_simulation_set
and instance_id = p_downtime_row.instance_id
and serial_number = p_downtime_row.eqp_serial_number
and action_type = p_action_type
and ( ( (trunc(from_date)+(from_time/86400))
between l_from_date_new and l_to_date_new)
or
(( trunc(to_date)+(to_time/86400))
between l_from_date_new and l_to_date_new)
or
( l_from_date_new between
(trunc(from_date)+(from_time/86400))
and
(trunc(to_date)+(to_time/86400)))
or
(l_to_date_new between
(trunc(from_date)+(from_time/86400))
and
(trunc(to_date)+(to_time/86400))));
-- hence no update and no insert is required.
p_out_to_update := FALSE; --to be skipped
-- Find new values for time range to be updated in database.
if ( l_from_date_new < l_from_date_old ) then
p_out_from_date := p_from_date;
p_out_to_update := TRUE;
p_out_to_update := TRUE;
p_out_to_update := FALSE;
* resource/department and inserts into bom_res_instance_changes
*/
procedure break_and_insert (p_from_date IN DATE,
p_from_time IN NUMBER,
p_to_date IN DATE,
p_to_time IN NUMBER,
p_shift_num IN NUMBER,
p_calendar_code IN VARCHAR2,
p_exception_set_id IN NUMBER,
p_simulation_set IN VARCHAR2,
p_downtime_group_id IN NUMBER,
p_downtime_row IN downtime_csr%ROWTYPE) is
-- Bug # 3787120 Modified cursor query
cursor c_wdays is
select bd.shift_date,bt.from_time,bt.to_time
from bom_shift_dates bd,bom_shift_times bt
where trunc(bd.shift_date) >= trunc(p_from_date)
and trunc(bd.shift_date) <= trunc(p_to_date)
and bd.calendar_code = p_calendar_code
and bd.shift_num = p_shift_num
and bd.exception_set_id= p_exception_set_id
and bd.seq_num is not null
and bt.calendar_code = bd.calendar_code
and bt.shift_num = bd.shift_num
order by bd.shift_date,bt.from_time;
select decode(nvl(bd.seq_num,-999),-999,bd.prior_date,bd.shift_date)
from bom_shift_dates bd
where trunc(bd.shift_date) = trunc(p_curr_date)-1
and bd.calendar_code = p_calendar_code
and bd.shift_num = p_shift_num
and bd.exception_set_id = p_exception_set_id;
l_to_insert boolean;
l_out_to_update boolean;
fnd_file.put_line(fnd_file.log,'In break_and_insert ');
l_to_insert := FALSE;
l_out_to_update := FALSE;
-- Need to insert / update the record
-- Update the date time value to fit in the shift timing of the resource
if (to_date(to_char(p_from_date,'DD-MM-YYYY ')||to_char(p_from_time),'DD-MM-YYYY SSSSS') >
to_date(to_char(l_shift_from_date,'DD-MM-YYYY ')||to_char(l_shift_from_time),'DD-MM-YYYY SSSSS'))
then
l_from_date := p_from_date;
l_out_to_update) = TRUE )
then
l_to_insert := FALSE;
l_to_insert := TRUE;
if l_to_insert = TRUE then
fnd_file.put_line(fnd_file.log,'To be inserted');
if l_out_to_update = TRUE then
fnd_file.put_line(fnd_file.log,'To be updated');
if ( l_to_insert = TRUE and l_out_to_update = FALSE ) then
if G_DEBUG = 'Y' THEN
fnd_file.put_line(fnd_file.log,'Inserting from '||l_from_date||to_char(to_date(l_from_time,'SSSSS'),' HH24:MI:SS')||
' to '||l_to_date||to_char(to_date(l_to_time,'SSSSS'),' HH24:MI:SS'));
insert into bom_res_instance_changes(
department_id,
resource_id,
shift_num,
simulation_set,
from_date,
from_time,
to_date,
to_time,
instance_id,
serial_number,
action_type,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
capacity_change,
reason_code,
downtime_source_code,
maintenance_organization_id,
wip_entity_id,
operation_seq_num,
downtime_group_id,
downtime_negotiable_flag)
values(
p_downtime_row.department_id,
p_downtime_row.resource_id,
p_shift_num,
p_simulation_set,
l_from_date,
l_from_time,
l_to_date,
l_to_time,
p_downtime_row.instance_id,
p_downtime_row.eqp_serial_number,
2, --reduce capacity
sysdate, --standard who
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
null, null, --descp flex
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
-1, --capacity change
g_reason_code,
p_downtime_row.downtime_source_code,
p_downtime_row.maint_org_id,
p_downtime_row.wip_entity_id,
p_downtime_row.op_seq,
p_downtime_group_id,
p_downtime_row.firm_planned_flag);
if ( l_out_to_update = TRUE and l_to_insert = FALSE ) then
if G_DEBUG = 'Y' THEN
fnd_file.put_line(fnd_file.log,'Updating...');
update bom_res_instance_changes
set from_date = l_out_from_date,
from_time = l_out_from_time,
to_date = l_out_to_date,
to_time = l_out_to_time
where department_id = p_downtime_row.department_id
and resource_id = p_downtime_row.resource_id
and shift_num = p_shift_num
and simulation_set = p_simulation_set
and instance_id = p_downtime_row.instance_id
and serial_number = p_downtime_row.eqp_serial_number
and action_type = 2
and from_date = l_out_from_date_old
and from_time = l_out_from_time_old
and to_date = l_out_to_date_old
and to_time = l_out_to_time_old;
end break_and_insert;
select bsd1.shift_num, bsd1.seq_num start_seq, bsd1.next_date next_date,
bsd2.seq_num end_seq, bsd2.prior_date prior_date
from bom_shift_dates bsd1, bom_shift_dates bsd2, bom_resource_shifts brs where
trunc(bsd1.shift_date) =trunc(pp_from_date)
and bsd1.calendar_code=p_calendar_code
and bsd1.exception_set_id=p_exception_set_id
and bsd1.shift_num=bsd2.shift_num
and trunc(bsd2.shift_date)=trunc(pp_to_date)
and bsd2.calendar_code=p_calendar_code
and bsd2.exception_set_id=p_exception_set_id
and brs.department_id= pp_department_id
and brs.resource_id = pp_resource_id
and brs.shift_num=bsd1.shift_num;
/* Moved the insert stmt on bom_res_instance_changes
* to procedure break_and_insert as part of fix for
* bug#3577299
*/
break_and_insert(l_from_date,l_from_time,l_to_date,l_to_time,
a_shift.shift_num,p_calendar_code,
p_exception_set_id,p_simulation_set,
p_downtime_group_id,l_out_downtime_tbl(j));
delete from bom_res_instance_changes
where
downtime_group_id is not null
and simulation_set=p_simulation_set
and ((p_department_id is not null and department_id=p_department_id)
or (p_department_id is null and department_id in
(select department_id from bom_departments
where organization_id=p_org_id)))
and ((p_resource_id is not null and resource_id=p_resource_id)
or (p_resource_id is null));
delete from bom_resource_changes
where
downtime_group_id is not null
and simulation_set=p_simulation_set
and ((p_department_id is not null and department_id=p_department_id)
or (p_department_id is null and department_id in
(select department_id from bom_departments
where organization_id=p_org_id)))
and ((p_resource_id is not null and resource_id=p_resource_id)
or (p_resource_id is null));
insert into bom_resource_changes(
DEPARTMENT_ID,
RESOURCE_ID,
SHIFT_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FROM_DATE,
TO_DATE,
FROM_TIME,
TO_TIME,
CAPACITY_CHANGE,
SIMULATION_SET,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID ,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ACTION_TYPE,
REASON_CODE,
downtime_group_id)
select
DEPARTMENT_ID,
RESOURCE_ID,
SHIFT_NUM,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
FROM_DATE,
TO_DATE,
FROM_TIME,
TO_TIME,
sum(CAPACITY_CHANGE),
SIMULATION_SET,
null, null, --descp flex
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
p_request_id ,
p_prog_app_id,
p_prog_id,
sysdate,
ACTION_TYPE,
g_reason_code,
p_downtime_group_id
from bom_res_instance_changes
where
downtime_group_id=p_downtime_group_id
group by DEPARTMENT_ID,
RESOURCE_ID,
SHIFT_NUM,
FROM_DATE,
TO_DATE,
FROM_TIME,
TO_TIME,
ACTION_TYPE,
SIMULATION_SET;
delete from bom_res_instance_changes
where
downtime_group_id is not null
and simulation_set=p_simulation_set
and department_id in
(select department_id from bom_departments
where
organization_id=p_org_id);
delete from bom_resource_changes
where
downtime_group_id is not null
and simulation_set=p_simulation_set
and department_id in
(select department_id from bom_departments
where
organization_id=p_org_id);