[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
Nvl(USE_FINITE_SCHEDULER,2),
Nvl(MATERIAL_CONSTRAINED,2),
Nvl(HORIZON_LENGTH,1)
INTO x_use_finite_scheduler,
x_material_constrained,
x_horizon_length
FROM WIP_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id;
p_delete_data IN NUMBER)
IS
x_date_from DATE := trunc(p_start_date);
if(p_delete_data = 1) then
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and trunc(shift_date) >= trunc(p_start_date)
and trunc(shift_date) <= trunc(p_cutoff_date);
p_delete_data IN NUMBER)
IS
x_date_from DATE := trunc(p_start_date);
if(p_delete_data = 1) then
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and trunc(shift_date) >= trunc(p_start_date)
and trunc(shift_date) <= trunc(p_cutoff_date);
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and resource_id = p_resource_id
and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and resource_id = p_resource_id
and instance_id = p_instance_id
and nvl(serial_number,-1) = nvl(p_serial_number,-1)
and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
* Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
* range specified by p_start_date and p_cutoff_date and for the passed
* in simulation_set identifier.
*/
PROCEDURE populate_mrp_avail_resources(p_simulation_set IN varchar2,
p_organization_id IN number,
p_start_date IN date,
p_cutoff_date IN date,
p_wip_entity_id IN number)
IS
x_department_id NUMBER;
select dept_res.department_id,
dept_res.resource_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
bom_departments dept
where dept_res.department_id = dept.department_id
AND dept_res.share_from_dept_id is null
AND dept.organization_id = p_organization_id;
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
dept_res.resource_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
wip_operations wo,
wip_operation_resources wor
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wor.wip_entity_id = wo.wip_entity_id
AND wor.organization_id = wo.organization_id
AND wor.operation_seq_num = wo.operation_seq_num
AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
AND dept_res.resource_id = wor.resource_id
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
dept_res.resource_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
wip_operations wo,
wip_sub_operation_resources wsor
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wsor.wip_entity_id = wo.wip_entity_id
AND wsor.organization_id = wo.organization_id
AND wsor.operation_seq_num = wo.operation_seq_num
AND dept_res.department_id = nvl(wsor.department_id, wo.department_id)
AND dept_res.resource_id = wsor.resource_id
union
select distinct bdr.department_id department_id,
bdr.resource_id,
nvl(bdr.available_24_hours_flag, 2)
from bom_std_op_resources bsor,
bom_standard_operations bso,
bom_department_resources bdr,
bom_setup_transitions bst,
wip_operation_resources wor
WHERE wor.organization_id = p_organization_id
AND wor.wip_entity_id = p_wip_entity_id
AND wor.setup_id is not null
AND bst.resource_id = wor.resource_id
AND bst.to_setup_id = wor.setup_id
AND bso.standard_operation_id = bst.operation_id
AND bsor.standard_operation_id = bso.standard_operation_id
AND bdr.department_id = bso.department_id
AND bdr.resource_id = bsor.resource_id
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
dept_res.resource_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
wip_sub_operation_resources wsor,
bom_setup_transitions bst,
bom_standard_operations bso,
bom_std_op_resources bsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.organization_id = p_organization_id
and wsor.setup_id is not null
and bst.resource_id = wsor.resource_id
and bst.to_setup_id = wsor.setup_id
and bso.standard_operation_id = bst.operation_id
and bsor.standard_operation_id = bso.standard_operation_id
and dept_res.department_id = bso.department_id
and dept_res.resource_id = bsor.resource_id;
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and trunc(shift_date) >= trunc(p_start_date)
and trunc(shift_date) <= trunc(p_cutoff_date);
* Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
* range specified by p_start_date and p_cutoff_date and for the passed
* in simulation_set identifier.
*/
PROCEDURE populate_mrp_avail_res_inst
(p_simulation_set IN varchar2,
p_organization_id IN number,
p_start_date IN date,
p_cutoff_date IN date,
p_wip_entity_id IN number)
IS
x_department_id NUMBER;
select dept_ins.department_id,
NVL(dept_res.available_24_hours_flag, 2),
dept_ins.resource_id,
dept_ins.instance_id,
dept_ins.serial_number
from bom_dept_res_instances dept_ins,
bom_department_resources dept_res,
bom_departments dept
where dept_res.department_id = dept.department_id
AND dept_res.share_from_dept_id is null
AND dept_ins.resource_id = dept_res.resource_id
AND dept_ins.department_id = dept_res.department_id
AND dept.organization_id = p_organization_id;
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
NVL(dept_res.available_24_hours_flag, 2),
dept_res.resource_id,
dept_ins.instance_id,
dept_ins.serial_number
from bom_department_resources dept_res,
bom_dept_res_instances dept_ins,
wip_operations wo,
wip_operation_resources wor
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wor.wip_entity_id = wo.wip_entity_id
AND wor.organization_id = wo.organization_id
AND wor.operation_seq_num = wo.operation_seq_num
AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
AND dept_res.resource_id = wor.resource_id
AND dept_ins.department_id = dept_res.department_id
AND dept_ins.resource_id = dept_res.resource_id
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
NVL(dept_res.available_24_hours_flag, 2),
dept_res.resource_id,
ins_changes.instance_id,
ins_changes.serial_number
from bom_department_resources dept_res,
bom_res_instance_changes ins_changes,
wip_operations wo,
wip_operation_resources wor
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wor.wip_entity_id = wo.wip_entity_id
AND wor.organization_id = wo.organization_id
AND wor.operation_seq_num = wo.operation_seq_num
AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
AND dept_res.resource_id = wor.resource_id
AND ins_changes.department_id = dept_res.department_id
AND ins_changes.resource_id = dept_res.resource_id
AND not exists
( select 1
from bom_dept_res_instances dept_ins
where dept_ins.department_id = ins_changes.department_id
and dept_ins.resource_id = ins_changes.resource_id
and dept_ins.instance_id = ins_changes.instance_id
and nvl(dept_ins.serial_number, -1) = nvl(ins_changes.serial_number, -1))
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
NVL(dept_res.available_24_hours_flag, 2),
dept_res.resource_id,
dept_ins.instance_id,
dept_ins.serial_number
from bom_department_resources dept_res,
bom_dept_res_instances dept_ins,
wip_operations wo,
wip_sub_operation_resources wsor
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wsor.wip_entity_id = wo.wip_entity_id
AND wsor.organization_id = wo.organization_id
AND wsor.operation_seq_num = wo.operation_seq_num
AND dept_res.department_id = nvl(wsor.department_id, wo.department_id)
AND dept_res.resource_id = wsor.resource_id
AND dept_ins.department_id = dept_res.department_id
AND dept_ins.resource_id = dept_res.resource_id
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
NVL(dept_res.available_24_hours_flag, 2),
dept_res.resource_id,
dept_ins.instance_id,
dept_ins.serial_number
from bom_department_resources dept_res,
bom_dept_res_instances dept_ins,
wip_operation_resources wor,
bom_setup_transitions bst,
bom_standard_operations bso,
bom_std_op_resources bsor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and wor.setup_id is not null
and bst.resource_id = wor.resource_id
and bst.to_setup_id = wor.setup_id
and bso.standard_operation_id = bst.operation_id
and bsor.standard_operation_id = bso.standard_operation_id
and dept_res.department_id = bso.department_id
and dept_res.resource_id = bsor.resource_id
AND dept_ins.department_id = dept_res.department_id
AND dept_ins.resource_id = dept_res.resource_id
union
select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
NVL(dept_res.available_24_hours_flag, 2),
dept_res.resource_id,
dept_ins.instance_id,
dept_ins.serial_number
from bom_department_resources dept_res,
bom_dept_res_instances dept_ins,
wip_sub_operation_resources wsor,
bom_setup_transitions bst,
bom_standard_operations bso,
bom_std_op_resources bsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.organization_id = p_organization_id
and wsor.setup_id is not null
and bst.resource_id = wsor.resource_id
and bst.to_setup_id = wsor.setup_id
and bso.standard_operation_id = bst.operation_id
and bsor.standard_operation_id = bso.standard_operation_id
and dept_res.department_id = bso.department_id
and dept_res.resource_id = bsor.resource_id
AND dept_ins.department_id = dept_res.department_id
AND dept_ins.resource_id = dept_res.resource_id;
delete from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and trunc(shift_date) >= trunc(p_start_date)
and trunc(shift_date) <= trunc(p_cutoff_date);
* Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
* range specified by p_start_date and p_cutoff_date and for the passed
* in simulation_set identifier.
*/
PROCEDURE populate_single_mrp_avail_res(p_simulation_set IN varchar2,
p_organization_id IN number,
p_resource_id IN number,
p_start_date IN date,
p_cutoff_date IN date,
p_department_id IN NUMBER)
IS
x_department_id NUMBER;
select dept_res.department_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_department_resources dept_res,
bom_departments dept
where dept_res.department_id = dept.department_id
AND dept_res.resource_id = p_resource_id
AND dept_res.share_from_dept_id is null
AND dept.organization_id = p_organization_id
AND decode(p_department_id,null,-1,dept.department_id) = nvl(p_department_id,-1);
* Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
* range specified by p_start_date and p_cutoff_date and for the passed
* in simulation_set identifier.
*/
PROCEDURE populate_single_mrp_avail_ins(p_simulation_set IN varchar2,
p_organization_id IN number,
p_resource_id IN number,
p_instance_id IN number,
p_serial_number IN varchar2,
p_start_date IN date,
p_cutoff_date IN date,
p_department_id IN NUMBER)
IS
x_department_id NUMBER;
select dept_ins.department_id,
NVL(dept_res.available_24_hours_flag, 2)
from bom_dept_res_instances dept_ins,
bom_department_resources dept_res,
bom_departments dept
where dept_ins.department_id = dept.department_id
AND dept_res.department_id = dept_ins.department_id
and dept_res.resource_id = p_resource_id
AND dept_ins.resource_id = p_resource_id
AND dept_ins.instance_id = p_instance_id
AND dept.organization_id = p_organization_id
AND decode(p_department_id,null,-1,dept.department_id) = nvl(p_department_id,-1);
select trunc(min(shift_date)), trunc(max(shift_date))
into max_date_from, max_date_to
from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set;
select trunc(min(shift_date)), trunc(max(shift_date))
into max_date_from, max_date_to
from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and resource_id = p_resource_id
and instance_id is null
and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
select trunc(min(shift_date)), trunc(max(shift_date))
into max_date_from, max_date_to
from mrp_net_resource_avail
where organization_id = p_organization_id
and simulation_set = p_simulation_set
and resource_id = p_resource_id
and instance_id = p_instance_id
and nvl(serial_number, -1)= nvl(p_serial_number, -1)
and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
SELECT WIP_PROCESSING_BATCH_S.NEXTVAL INTO dummy FROM DUAL;
SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WIP_SCHED_DIRECTION'
AND ENABLED_FLAG = 'Y'
AND sysdate BETWEEN
NVL(START_DATE_ACTIVE, sysdate-1) AND NVL(END_DATE_ACTIVE, sysdate + 1)
AND LOOKUP_CODE IN (1,4)
ORDER BY LOOKUP_CODE;
SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND ENABLED_FLAG = 'Y'
AND sysdate BETWEEN
NVL(START_DATE_ACTIVE, sysdate-1) AND NVL(END_DATE_ACTIVE, sysdate + 1)
ORDER BY LOOKUP_CODE;
select 'T'
into ret
from dual
where exists (
select 1
from HZ_CUST_ACCOUNTS cust_accnt, HZ_PARTIES cust_party,
mtl_reservations mr, oe_order_lines_all ool
where mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and cust_party.party_name like p_cust_name
and cust_accnt.cust_account_id = ool.sold_to_org_id
and cust_party.party_id = cust_accnt.party_id
and mr.supply_source_header_id = p_wip_entity_id
);
select 'T'
into ret
from dual
where exists (
select 1
from mtl_reservations mr , mtl_sales_orders mso
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and mso.segment1 like p_so_name
and mr.supply_source_header_id = p_wip_entity_id
);
select count(distinct ool.sold_to_org_id), count(distinct mr.demand_source_header_id)
into cust_cnt, so_cnt
from mtl_reservations mr, oe_order_lines_all ool, wip_discrete_jobs wdj
where mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and mr.supply_source_header_id = p_wip_entity_id
and wdj.wip_entity_id = p_wip_entity_id
and wdj.organization_id = mr.organization_id
and mr.inventory_item_id = wdj.primary_item_id;
select mso.segment1, cust_party.party_name
into so_name, cust_name
from mtl_reservations mr, oe_order_lines_all ool,
hz_cust_accounts cust_accnt, hz_parties cust_party,
mtl_sales_orders mso
where mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and cust_accnt.cust_account_id = ool.sold_to_org_id
and cust_party.party_id = cust_accnt.party_id
and mso.sales_order_id = mr.demand_source_header_id
and mr.supply_source_header_id = p_wip_entity_id
and rownum = 1;
procedure update_scheduling_request_id(p_request_id in NUMBER,
p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER)
IS
RESOURCE_BUSY EXCEPTION;
select wip_entity_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
for update nowait;
select wip_entity_id
into l_wid
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
for update nowait;
update wip_discrete_jobs
set scheduling_request_id = p_request_id
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
END update_scheduling_request_id;
procedure update_scheduling_request_id(p_request_id in NUMBER,
p_wip_entity_id_table IN Number_Tbl_Type,
p_wip_entity_table_size IN NUMBER,
p_organization_id NUMBER)
IS
i number := 1;
update_scheduling_request_id(p_request_id,
p_wip_entity_id_table(i),
p_organization_id);
END update_scheduling_request_id;
select start_quantity into start_qty
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT SUM(((WO.QUANTITY_COMPLETED+wo.QUANTITY_SCRAPPED)/wo.SCHEDULED_QUANTITY)*(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE)),
SUM(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE) into completed, total
from wip_operations wo,
wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id
and wo.wip_entity_id = wdj.wip_entity_id
and wo.organization_id = wdj.organization_id;
select ((QUANTITY_COMPLETED+ QUANTITY_SCRAPPED)/START_QUANTITY) INTO progress
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
procedure update_wip_op_resource -- for wip table
(
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_resource_seq_num IN NUMBER,
p_new_start_date IN DATE,
p_new_completion_date IN DATE,
x_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_start_date DATE;
select wor.start_date, wor.completion_date
into l_old_start_date, l_old_completion_date
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
for update;
select wor.schedule_seq_num
into l_sched_seq_num
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;
update wip_operation_resources wor
set wor.start_date = p_new_start_date,
wor.completion_date = p_new_completion_date,
wor.last_update_date = sysdate
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;
delete wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num = p_resource_seq_num;
update wip_op_resource_instances wori
set wori.start_date = p_new_start_date,
wori.completion_date = p_new_completion_date,
wori.last_update_date = sysdate
where wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = p_operation_seq_num
and wori.resource_seq_num = p_resource_seq_num;
update wip_operation_resources wor
set wor.start_date = wor.start_date + l_offset,
wor.completion_date = wor.completion_date + l_offset,
wor.last_update_date = sysdate
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_operation_seq_num
and wor.schedule_seq_num = l_sched_seq_num;
delete wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_operation_seq_num
and woru.resource_seq_num in
( select wor.resource_seq_num 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.schedule_seq_num = l_sched_seq_num
);
update wip_op_resource_instances wori
set wori.start_date = wori.start_date + l_offset,
wori.completion_date = wori.completion_date + l_offset,
wori.last_update_date = sysdate
where wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num in
( select wor.resource_seq_num 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.schedule_seq_num = l_sched_seq_num
);
/* check if operation start/completion dates need to be updated also */
/* here asume the first/last unit will be the same ??*/
select wo.first_unit_start_date, wo.last_unit_completion_date
into l_old_start_date, l_old_completion_date
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
for update;
update wip_operations wo
set wo.first_unit_start_date = l_new_start_date,
wo.last_unit_start_date = l_new_start_date,
wo.first_unit_completion_date = l_new_completion_date,
wo.last_unit_completion_date = l_new_completion_date,
wo.last_update_date = sysdate
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num;
/* check if job needs to be updated */
select wdj.scheduled_start_date, wdj.scheduled_completion_date
into l_old_start_date, l_old_completion_date
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id
for update;
update wip_discrete_jobs wdj
set wdj.scheduled_start_date = l_new_start_date,
wdj.scheduled_completion_date = l_new_completion_date,
wdj.last_update_date = sysdate
where wdj.wip_entity_id = p_wip_entity_id;
END update_wip_op_resource;
procedure update_wsm_copy_op_resource -- for wip table
(
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_resource_seq_num IN NUMBER,
p_new_start_date IN DATE,
p_new_completion_date IN DATE,
x_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_start_date DATE;
select wcor.reco_start_date, wcor.reco_completion_date
into l_old_start_date, l_old_completion_date
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.resource_seq_num = p_resource_seq_num
for update;
select wcor.schedule_seq_num
into l_sched_seq_num
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.resource_seq_num = p_resource_seq_num;
update wsm_copy_op_resources wcor
set wcor.reco_start_date = p_new_start_date,
wcor.reco_completion_date = p_new_completion_date,
wcor.last_update_date = sysdate
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.resource_seq_num = p_resource_seq_num;
delete wsm_copy_op_resource_usage wcoru
where wcoru.wip_entity_id = p_wip_entity_id
and wcoru.operation_seq_num = p_operation_seq_num
and wcoru.resource_seq_num = p_resource_seq_num;
update wsm_copy_op_resource_instances wcori
set wcori.start_date = p_new_start_date,
wcori.completion_date = p_new_completion_date,
wcori.last_update_date = sysdate
where wcori.wip_entity_id = p_wip_entity_id
and wcori.operation_seq_num = p_operation_seq_num
and wcori.resource_seq_num = p_resource_seq_num;
update wsm_copy_op_resources wcor
set wcor.reco_start_date = wcor.reco_start_date + l_offset,
wcor.reco_completion_date = wcor.reco_completion_date + l_offset,
wcor.last_update_date = sysdate
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.schedule_seq_num = l_sched_seq_num
and wcor.recommended = 'Y';
delete wsm_copy_op_resource_usage wcoru
where wcoru.wip_entity_id = p_wip_entity_id
and wcoru.operation_seq_num = p_operation_seq_num
and wcoru.resource_seq_num in
( select wcor.resource_seq_num
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.schedule_seq_num = l_sched_seq_num
);
/* update instances if have any */
update wsm_copy_op_resource_instances wcori
set wcori.start_date = wcori.start_date + l_offset,
wcori.completion_date = wcori.completion_date + l_offset,
wcori.last_update_date = sysdate
where wcori.wip_entity_id = p_wip_entity_id
and wcori.operation_seq_num = p_operation_seq_num
and wcori.resource_seq_num in
( select wcor.resource_seq_num
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_operation_seq_num
and wcor.schedule_seq_num = l_sched_seq_num
);
/* check if operation start/completion dates need to be updated also */
/* here asume the first/last unit will be the same ??*/
select wco.reco_start_date, wco.reco_completion_date
into l_old_start_date, l_old_completion_date
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = p_operation_seq_num
for update;
update wsm_copy_operations wco
set wco.reco_start_date = l_new_start_date,
wco.reco_completion_date = l_new_completion_date,
wco.last_update_date = sysdate
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = p_operation_seq_num;
/* check if job needs to be updated */
select wdj.scheduled_start_date, wdj.scheduled_completion_date
into l_old_start_date, l_old_completion_date
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id
for update;
update wip_discrete_jobs wdj
set wdj.scheduled_start_date = l_new_start_date,
wdj.scheduled_completion_date = l_new_completion_date,
wdj.last_update_date = sysdate
where wdj.wip_entity_id = p_wip_entity_id;
END update_wsm_copy_op_resource;
PROCEDURE update_operation_resource
(
p_entity_type IN NUMBER,
p_source IN NUMBER,
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_resource_seq_num IN NUMBER,
p_new_start_date IN DATE,
p_new_completion_date IN DATE,
x_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_start_date DATE;
update_wip_op_resource(
p_wip_entity_id,
p_operation_seq_num,
p_resource_seq_num,
p_new_start_date,
p_new_completion_date,
x_status,
x_msg_count,
x_msg_data);
update_wip_op_resource(
p_wip_entity_id,
p_operation_seq_num,
p_resource_seq_num,
p_new_start_date,
p_new_completion_date,
x_status,
x_msg_count,
x_msg_data);
update_wsm_copy_op_resource(
p_wip_entity_id,
p_operation_seq_num,
p_resource_seq_num,
p_new_start_date,
p_new_completion_date,
x_status,
x_msg_count,
x_msg_data);
End update_operation_resource;
SELECT NVL(SUM(QUANTITY),0)
FROM MTL_SECONDARY_INVENTORIES MSS,
MTL_ITEM_QUANTITIES_VIEW MOQ,
MTL_SYSTEM_ITEMS MSI
WHERE MOQ.ORGANIZATION_ID = p_organization_id
AND MSI.ORGANIZATION_ID = p_organization_id
AND MSS.ORGANIZATION_ID = p_organization_id
AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
AND MSS.AVAILABILITY_TYPE = 1;
SELECT NVL(SUM(QUANTITY),0)
FROM MTL_ITEM_QUANTITIES_VIEW MOQ
WHERE MOQ.ORGANIZATION_ID = p_organization_id
AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id;