[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 hca, hz_parties hp,
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 hp.party_name like p_cust_name
and hca.cust_account_id = ool.sold_to_org_id
and hp.party_id = hca.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, hp.party_name
into so_name, cust_name
from mtl_reservations mr, oe_order_lines_all ool,
hz_cust_accounts hca, hz_parties hp,
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 hca.cust_account_id = ool.sold_to_org_id
and hp.party_id = hca.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 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;