[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct cal.calendar_date
FROM mtl_parameters mp,
bom_calendar_dates cal
WHERE mp.organization_id = p_org_id
AND cal.calendar_date >= p_start_date
AND cal.seq_num IS NOT NULL
AND mp.calendar_exception_set_id = cal.exception_set_id
AND mp.calendar_code = cal.calendar_code
UNION ALL
SELECT distinct cal.calendar_date
FROM mtl_parameters mp,
mrp_line_sch_avail_v ls,
bom_calendar_dates cal
WHERE mp.organization_id = p_org_id
AND ls.line_id = p_line_id
AND cal.calendar_date >= p_start_date
AND ((ls.planned_quantity - nvl(ls.quantity_completed,0)) > 0 and
cal.seq_num IS NULL)
AND cal.calendar_date = ls.scheduled_completion_date
AND ls.organization_id = p_org_id
AND mp.calendar_exception_set_id = cal.exception_set_id
AND mp.calendar_code = cal.calendar_code;
SELECT bdr.department_id,
bdr.resource_id,
-1, -- line_id
mfq.number1,
cal.calendar_date,
decode(cal.seq_num, NULL, 0,
nvl(sum(decode(bdr.available_24_hours_flag,1,24,
((decode(least(shifts.to_time,shifts.from_time),
shifts.to_time,shifts.to_time + 24*3600,
shifts.to_time) - shifts.from_time)/3600))),0))
FROM bom_calendar_dates cal,
bom_department_resources bdr,
bom_resource_shifts brs,
bom_shift_times shifts,
mtl_parameters mp,
mrp_form_query mfq
WHERE bdr.department_id = p_dept_id
AND bdr.resource_id = p_res_id
AND bdr.department_id = brs.department_id(+)
AND bdr.resource_id = brs.resource_id(+)
AND brs.shift_num = shifts.shift_num(+)
AND (mp.calendar_code = shifts.calendar_code
OR shifts.calendar_code IS NULL)
AND cal.seq_num IS NOT NULL
AND cal.exception_set_id = mp.calendar_exception_set_id
AND cal.calendar_code = mp.calendar_code
AND mp.organization_id = p_org_id
AND trunc(cal.calendar_date) = mfq.date1
AND mfq.query_id = p_query_id
GROUP BY bdr.department_id,
bdr.resource_id,
mfq.number1,
cal.calendar_date,
cal.seq_num;
SELECT bos.department_id,
br.resource_id,
-1, -- line_id
mfq.number1,
cal_start.calendar_date,
sum(br.usage_rate_or_amount * (nvl(bos1.net_planning_percent, 100)/100) /
nvl(bos1.reverse_cumulative_yield, 1) * /*Fix for bug 2000775*/
decode(br.basis_type,1,ls.planned_quantity -
nvl(ls.quantity_completed,0) , 2, 1))
FROM bom_calendar_dates cal_start,
bom_calendar_dates cal_order,
mtl_system_items items,
mtl_parameters mp,
mrp_form_query mfq,
bom_operation_resources br,
bom_operation_sequences bos,
bom_operation_sequences bos1,
bom_resources bre,
mtl_units_of_measure muom,
bom_operational_routings bor,
mrp_line_sch_avail_v ls
WHERE ls.line_id = p_line_id
AND ls.organization_id = p_org_id
AND nvl(ls.alternate_routing_designator,'@!#')
= nvl(bor.alternate_routing_designator,'@!#')
AND bor.line_id = ls.line_id
AND bor.assembly_item_id = ls.primary_item_id
AND bor.organization_id = ls.organization_id
AND bos.routing_sequence_id = bor.routing_sequence_id
AND bos.operation_type = 1
AND bos1.operation_sequence_id = bos.line_op_seq_id
AND br.operation_sequence_id = bos.operation_sequence_id
-- AND br.schedule_flag <> 2
AND br.resource_id = bre.resource_id
AND bre.unit_of_measure = muom.uom_code
AND muom.uom_class = p_uom_class
AND cal_start.exception_set_id = cal_order.exception_set_id
AND cal_start.calendar_code = cal_order.calendar_code
AND cal_start.seq_num = (cal_order.prior_seq_num -
CEIL((1 - NVL(br.resource_offset_percent,0)) *
(NVL(items.fixed_lead_time,0) +
(NVL(items.variable_lead_time,0) *
NVL(ls.planned_quantity,0)))))
AND cal_order.exception_set_id = mp.calendar_exception_set_id
AND cal_order.calendar_code = mp.calendar_code
AND cal_order.calendar_date = trunc(ls.scheduled_completion_date)
AND mp.organization_id = ls.organization_id
AND items.organization_id = ls.organization_id
AND items.inventory_item_id = ls.primary_item_id
AND trunc(ls.scheduled_completion_date) = mfq.date1
AND mfq.query_id = p_query_id
GROUP BY bos.department_id,
br.resource_id,
mfq.number1,
cal_start.calendar_date
UNION
SELECT -1, -- department_id
-1, -- resource_id
ls.line_id,
mfq.number1,
mfq.date1,
sum(ls.planned_quantity - nvl(ls.quantity_completed,0))
FROM mrp_form_query mfq,
mrp_line_sch_avail_v ls
WHERE ls.organization_id = p_org_id
AND ls.line_id = p_line_id
AND trunc(ls.scheduled_completion_date) = mfq.date1
AND mfq.query_id = p_query_id
GROUP BY -1,-1,
ls.line_id,
mfq.number1,
mfq.date1;
SELECT NVL(maximum_rate * ((decode(least(stop_time,start_time),stop_time,stop_time+24*3600,stop_time)-start_time)/3600),0)
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
INSERT INTO MRP_MATERIAL_PLANS (
plan_id, -- unique identifier
plan_organization_id, -- line_id
inventory_item_id, -- resource_id
organization_id, -- department_id
item_segments, -- description
horizontal_plan_type, -- type
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity1, quantity2, quantity3, quantity4, quantity5, quantity6,
quantity7, quantity8, quantity9, quantity10, quantity11, quantity12,
quantity13, quantity14, quantity15, quantity16, quantity17, quantity18,
quantity19, quantity20, quantity21, quantity22, quantity23, quantity24,
quantity25, quantity26, quantity27, quantity28, quantity29, quantity30,
quantity31, quantity32, quantity33, quantity34, quantity35, quantity36,
compile_designator, -- not used
bucket_type -- not used
) values (
p_plan_id,
p_local_line_id,
p_res_id,
p_dept_id,
' ',
p_supply_demand,
sysdate,
-1,
sysdate,
-1,
qty_cells_tab(1), qty_cells_tab(2), qty_cells_tab(3),
qty_cells_tab(4), qty_cells_tab(5), qty_cells_tab(6),
qty_cells_tab(7), qty_cells_tab(8), qty_cells_tab(9),
qty_cells_tab(10), qty_cells_tab(11), qty_cells_tab(12),
qty_cells_tab(13), qty_cells_tab(14), qty_cells_tab(15),
qty_cells_tab(16), qty_cells_tab(17), qty_cells_tab(18),
qty_cells_tab(19), qty_cells_tab(20), qty_cells_tab(21),
qty_cells_tab(22), qty_cells_tab(23), qty_cells_tab(24),
qty_cells_tab(25), qty_cells_tab(26), qty_cells_tab(27),
qty_cells_tab(28), qty_cells_tab(29), qty_cells_tab(30),
qty_cells_tab(31), qty_cells_tab(32), qty_cells_tab(33),
qty_cells_tab(34), qty_cells_tab(35), qty_cells_tab(36),
' ',
0
);
INSERT INTO MRP_WORKBENCH_BUCKET_DATES(
organization_id, -- plan_id
compile_designator, -- not used
bucket_type, -- not used
last_update_date, last_updated_by, creation_date, created_by,
date1, date2, date3, date4, date5, date6, date7, date8, date9,
date10, date11, date12, date13, date14, date15, date16, date17, date18,
date19, date20, date21, date22, date23, date24, date25, date26, date27,
date28, date29, date30, date31, date32, date33, date34, date35, date36
) VALUES (
p_plan_id,
' ',
1,
sysdate, -1, sysdate, -1,
date_cells_tab(1), date_cells_tab(2), date_cells_tab(3),
date_cells_tab(4), date_cells_tab(5), date_cells_tab(6),
date_cells_tab(7), date_cells_tab(8), date_cells_tab(9),
date_cells_tab(10), date_cells_tab(11), date_cells_tab(12),
date_cells_tab(13), date_cells_tab(14), date_cells_tab(15),
date_cells_tab(16), date_cells_tab(17), date_cells_tab(18),
date_cells_tab(19), date_cells_tab(20), date_cells_tab(21),
date_cells_tab(22), date_cells_tab(23), date_cells_tab(24),
date_cells_tab(25), date_cells_tab(26), date_cells_tab(27),
date_cells_tab(28), date_cells_tab(29), date_cells_tab(30),
date_cells_tab(31), date_cells_tab(32), date_cells_tab(33),
date_cells_tab(34), date_cells_tab(35), date_cells_tab(36)
);
select capacity_units
into temp_cap_units
from bom_department_resources
where department_id = l_dept_id
and resource_id = l_res_id;
SELECT mrp_form_query_s.nextval
INTO l_query_id
FROM dual;
INSERT INTO MRP_FORM_QUERY(QUERY_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, DATE1,
NUMBER1)
VALUES(l_query_id, sysdate, -1, sysdate, -1,
l_bucket_date, l_bucket_number);
select uom_class
into l_uom_class
from mtl_units_of_measure
where uom_code = l_uom_code;
select unit_of_measure into temp_uom from bom_resources
where resource_id = l_last_res_id;