[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_WORK_DAY CONSTANT INTEGER := 1;
SELECT daily_material_constraints,
daily_resource_constraints,
weekly_material_constraints,
weekly_resource_constraints,
period_material_constraints,
period_resource_constraints
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT next_seq_num
FROM msc_calendar_dates
WHERE calendar_code = g_calendar_code
AND exception_set_id = g_exc_set_id
AND sr_instance_id = g_inst_id
AND calendar_date = p_date;
SELECT prior_seq_num
FROM msc_calendar_dates
WHERE calendar_code = g_calendar_code
AND exception_set_id = g_exc_set_id
AND sr_instance_id = g_inst_id
AND calendar_date = p_date;
SELECT
crp.organization_id,
crp.sr_instance_id,
to_number(0),
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(crp.start_date),
trunc(crp.end_date),
avg(crp.resource_hours),
to_number(-1),
to_char('-1')
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_resource_batches mrb,
msc_supplies mss,
msc_form_query list
WHERE
nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
and nvl(cpr.batchable_flag,2) =1
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get Run Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
AND crp.plan_id = mss.plan_id
AND crp.supply_id = mss.transaction_id
and mrb.plan_id = crp.plan_id
and mrb.sr_instance_id = crp.sr_instance_id
and mrb.organization_id= crp.organization_id
and mrb.department_id = crp.department_id
and mrb.resource_id = crp.resource_id
and mrb.batch_number = crp.batch_number
GROUP BY crp.organization_id,
crp.sr_instance_id,
list.number2,
crp.resource_id,
trunc(crp.start_date),
trunc(crp.end_date),
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(crp.start_date),
trunc(crp.end_date),
cpr.line_flag
union all
*/
-- =============================
-- Batchable Resource requirements SETUP TIME
-- =============================
SELECT
crp.organization_id,
crp.sr_instance_id,
-- crp.assembly_item_id,
to_number(0),
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(crp.start_date),
trunc(crp.end_date),
avg(crp.resource_hours),
to_number(-1),
to_char('-1')
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_resource_batches mrb,
msc_supplies mss,
msc_form_query list
WHERE
nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
and nvl(cpr.batchable_flag,2) =1
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
AND crp.plan_id = mss.plan_id
AND crp.supply_id = mss.transaction_id
and mrb.plan_id = crp.plan_id
and mrb.sr_instance_id = crp.sr_instance_id
and mrb.organization_id= crp.organization_id
and mrb.department_id = crp.department_id
and mrb.resource_id = crp.resource_id
and mrb.batch_number = crp.batch_number
GROUP BY crp.organization_id,
crp.sr_instance_id,
-- crp.assembly_item_id,
list.number2,
crp.resource_id,
trunc(crp.start_date),
trunc(crp.end_date),
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(crp.start_date),
trunc(crp.end_date),
cpr.line_flag
union all
-- =============================
-- Dept/Resource requirements RUN TIME
-- =============================
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(crp.start_date),
trunc(crp.end_date),
decode (cpr.line_flag,
2, sum(decode(crp.end_date,
NULL, crp.resource_hours,
crp.daily_resource_hours)),
sum((cpr.max_rate*crp.daily_resource_hours))),
to_number(-1),
to_char('-1')
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_form_query list
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
-- and nvl(cpr.batchable_flag,2) =2
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get Run Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(crp.start_date),
trunc(crp.end_date),
cpr.line_flag
UNION ALL
-- -----------------------------------
-- To get Dept / Res Setup Time
-- -----------------------------------
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(crp.start_date),
trunc(crp.end_date),
decode (cpr.line_flag,
2, sum(decode(crp.end_date,
NULL, crp.resource_hours,
crp.daily_resource_hours)),
sum((cpr.max_rate*crp.daily_resource_hours))),
to_number(-1),
to_char('-1')
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_form_query list
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
and nvl(cpr.batchable_flag,2) =2
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(crp.start_date),
trunc(crp.end_date),
cpr.line_flag
UNION ALL
-- ===================================
-- Repetitive schedule line requirements
-- ===================================
SELECT
msrs.organization_id,
msrs.sr_instance_id,
msrs.inventory_item_id,
msrs.line_id,
-1,
REPETITIVE,
trunc(msrs.first_unit_start_date),
trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
msrs.daily_rate, -- this might not be right, used to be load_factor_rate
to_number(-1),
to_char('-1')
FROM msc_form_query list,
msc_supplies msrs
WHERE trunc(msrs.first_unit_start_date) <= trunc(g_cutoff_date)
AND msrs.plan_id = g_designator
AND msrs.organization_id = list.number1
AND msrs.sr_instance_id = list.number4
AND msrs.line_id = list.number2
AND list.number3 = -1
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Availability for Dept/Res
-- ===================================
SELECT
avail.organization_id,
avail.sr_instance_id,
TO_NUMBER(NULL),
NVL(avail.department_id,-1),
NVL(avail.resource_id,-1),
AVAILABLE_HOURS,
trunc(avail.shift_date),
to_date(NULL),
sum(avail.capacity_units * decode(from_time,NULL,1,(( DECODE(sign(avail.to_time-avail.from_time), -1, avail.to_time+86400, avail.to_time) - avail.from_time)/3600))),
to_number(-1),
to_char('-1')
FROM msc_net_resource_avail avail,
msc_form_query list
WHERE trunc(avail.shift_date) <= trunc(g_cutoff_date)
AND avail.shift_date >= g_dates(1)+1
AND NVL(avail.parent_id,0) <> -1
AND avail.plan_id = g_designator
AND avail.organization_id = list.number1
AND avail.sr_instance_id = list.number4
AND avail.department_id = list.number2
AND avail.resource_id = list.number3
AND avail.capacity_units >= 0
AND list.query_id = g_item_list_id
/*
AND not exists (select 'aggregate' from msc_net_resource_avail b
where b.shift_date = decode(v_agg_flag,1,null,avail.shift_date)
and b.resource_id = decode(v_agg_flag,1,null,avail.aggregate_resource_id)
and b.department_id = decode(v_agg_flag,1,null,avail.department_id)
and b.plan_id = avail.plan_id
and b.organization_id = decode(v_agg_flag,1,null,avail.organization_id)
and b.sr_instance_id = decode(v_agg_flag,1,null,avail.sr_instance_id))
*/
GROUP BY avail.organization_id, avail.sr_instance_id,
NVL(avail.department_id,-1), NVL(avail.resource_id,-1),
trunc(avail.shift_date)
UNION ALL
-------------------------------------------------------------------
-- This is to intoduce a new row in capacity HP 'ATP'
-- this row will reflect the net availability from ATP perspective
-- the cursor will select requirement from atp perspecive and
-- in calculate_cum we will calculate atp net
-------------------------------------------------------------------
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
ATP_REQUIRED_HOURS,
decode(g_optimized_plan, 1, trunc(crp.start_date),
nvl(trunc(crp.end_date),trunc(crp.start_date))),
trunc(crp.end_date),
sum(decode( nvl(cpr.batchable_flag,2), 1 ,
s.new_order_quantity * decode(cpr.uom_class_type,1,
i.unit_weight, i.unit_volume)
* nvl(decode(crp.end_date,NULL, crp.resource_hours,
crp.daily_resource_hours),0),
decode(cpr.line_flag,
2, decode(crp.end_date,
NULL, crp.resource_hours,
crp.daily_resource_hours),
cpr.max_rate*crp.daily_resource_hours))),
TO_NUMBER(-1),
to_char('-1')
FROM msc_resource_requirements crp,
msc_department_resources cpr,
msc_form_query list,
msc_supplies s,
msc_system_items i
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND NVL(crp.parent_id, g_optimized_plan) =
decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
AND crp.supply_id = s.transaction_id
AND crp.assembly_item_id = i.inventory_item_id
AND crp.sr_instance_id = i.sr_instance_id
AND crp.organization_id = i.organization_id
AND crp.plan_id = i.plan_id
AND i.inventory_item_id = s.inventory_item_id
AND i.organization_id = s.organization_id
AND i.sr_instance_id = s.sr_instance_id
AND i.plan_id = s.plan_id
AND ((i.bom_item_type <> 1 and i.bom_item_type <> 2) OR
(i.atp_flag ='Y') OR
(i.bom_item_type in (1, 2) AND s.record_source = 2) )
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
trunc(crp.start_date),
trunc(crp.end_date)
union all
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT list.number1,
list.number4,
list.number5,
list.number2,
list.number3,
PLANNED_ORDER,
to_date(1, 'J'),
to_date(1, 'J'),
0,
to_NUMBER(-1),
to_char('-1')
FROM msc_form_query list
WHERE list.query_id = g_item_list_id
ORDER BY 1,2,4,5,7,6;
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(mrir.start_date),
trunc(mrir.end_date),
decode (cpr.line_flag,
2, sum(decode(mrir.end_date,
NULL, mrir.resource_instance_hours,
mrir.daily_res_instance_hours)),
sum((cpr.max_rate*mrir.daily_res_instance_hours))),
mrir.res_instance_id,
mrir.serial_number
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_dept_res_instances mdri,
msc_resource_instance_reqs mrir,
msc_form_query list
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND mrir.res_instance_id = list.number8
AND mrir.serial_number = list.char3
and nvl(cpr.batchable_flag,2) =2
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get RUN Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
and mrir.plan_id = crp.plan_id
and mrir.sr_instance_id = crp.sr_instance_id
and mrir.organization_id = crp.organization_id
and mrir.supply_id = crp.supply_id
and mrir.resource_seq_num = crp.resource_seq_num
and mrir.operation_seq_num = crp.operation_seq_num
and mdri.plan_id = mrir.plan_id
and mdri.sr_instance_id = mrir.sr_instance_id
and mdri.organization_id = mrir.organization_id
and mdri.department_id = mrir.department_id
and mdri.resource_id = mrir.resource_id
and mdri.res_instance_id = mrir.res_instance_id
and mdri.serial_number = mrir.serial_number
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_RUN,
M_REPETITIVE, REPETITIVE_RUN,
M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
M_NONSTD_JOBS, NONSTD_JOBS_RUN,
M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN),
trunc(mrir.start_date),
trunc(mrir.end_date),
cpr.line_flag,
mrir.res_instance_id,
mrir.serial_number
UNION ALL
-- -----------------------------------
-- To get Dept / Res INSTANCE Setup Time
-- -----------------------------------
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(mrir.start_date),
trunc(mrir.end_date),
decode (cpr.line_flag,
2, sum(decode(mrir.end_date,
NULL, mrir.resource_instance_hours,
mrir.daily_res_instance_hours)),
sum((cpr.max_rate*mrir.daily_res_instance_hours))),
mrir.res_instance_id,
mrir.serial_number
FROM
msc_resource_requirements crp,
msc_department_resources cpr,
msc_dept_res_instances mdri,
msc_resource_instance_reqs mrir,
msc_form_query list
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND mrir.res_instance_id = list.number8
AND mrir.serial_number = list.char3
and nvl(cpr.batchable_flag,2) =2
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
M_ATP_ADJUSTMENT)
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
and mrir.plan_id = crp.plan_id
and mrir.sr_instance_id = crp.sr_instance_id
and mrir.organization_id = crp.organization_id
and mrir.supply_id = crp.supply_id
and mrir.resource_seq_num = crp.resource_seq_num
and mrir.operation_seq_num = crp.operation_seq_num
and mdri.plan_id = mrir.plan_id
and mdri.sr_instance_id = mrir.sr_instance_id
and mdri.organization_id = mrir.organization_id
and mdri.department_id = mrir.department_id
and mdri.resource_id = mrir.resource_id
and mdri.res_instance_id = mrir.res_instance_id
and mdri.serial_number = mrir.serial_number
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
M_REPETITIVE, REPETITIVE_SETUP,
M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP),
trunc(mrir.start_date),
trunc(mrir.end_date),
cpr.line_flag,
mrir.res_instance_id,
mrir.serial_number
UNION ALL
-- ===================================
-- Repetitive schedule line requirements
-- ===================================
SELECT
msrs.organization_id,
msrs.sr_instance_id,
msrs.inventory_item_id,
msrs.line_id,
-1,
REPETITIVE,
trunc(msrs.first_unit_start_date),
trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
msrs.daily_rate, -- this might not be right, used to be load_factor_rate
to_number(null),
to_char(null)
FROM msc_form_query list,
msc_supplies msrs
WHERE trunc(msrs.first_unit_start_date) <= trunc(g_cutoff_date)
AND msrs.plan_id = g_designator
AND msrs.organization_id = list.number1
AND msrs.sr_instance_id = list.number4
AND msrs.line_id = list.number2
AND list.number3 = -1
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Availability for Dept/Res INSTANCES
-- ===================================
SELECT
avail.organization_id,
avail.sr_instance_id,
TO_NUMBER(NULL),
NVL(avail.department_id,-1),
NVL(avail.resource_id,-1),
AVAILABLE_HOURS,
trunc(avail.shift_date),
to_date(NULL),
sum(nvl(avail.capacity_units,1) * decode(from_time,NULL,1,(( DECODE(sign(avail.to_time-avail.from_time), -1, avail.to_time+86400, avail.to_time) - avail.from_time)/3600))),
avail.res_instance_id,
avail.serial_number
FROM msc_net_res_inst_avail avail,
msc_form_query list
WHERE trunc(avail.shift_date) <= trunc(g_cutoff_date)
AND avail.shift_date >= g_dates(1)+1
AND NVL(avail.parent_id,0) <> -1
AND avail.plan_id = g_designator
AND avail.organization_id = list.number1
AND avail.sr_instance_id = list.number4
AND avail.department_id = list.number2
AND avail.resource_id = list.number3
AND avail.res_instance_id = list.number8
AND avail.serial_number = list.char3
AND nvl(avail.capacity_units,1) <> 0
AND list.query_id = g_item_list_id
-- AND not exists (select 'aggregate' from msc_net_resource_avail b
-- where b.shift_date = decode(v_agg_flag,1,null,avail.shift_date)
-- and b.resource_id = decode(v_agg_flag,1,null,avail.aggregate_resource_id)
-- and b.department_id = decode(v_agg_flag,1,null,avail.department_id)
-- and b.plan_id = avail.plan_id
-- and b.organization_id = decode(v_agg_flag,1,null,avail.organization_id)
-- and b.sr_instance_id = decode(v_agg_flag,1,null,avail.sr_instance_id))
GROUP BY avail.organization_id, avail.sr_instance_id,
NVL(avail.department_id,-1), NVL(avail.resource_id,-1),
trunc(avail.shift_date),
avail.res_instance_id,
avail.serial_number
UNION ALL
-------------------------------------------------------------------
-- This is to intoduce a new row in capacity HP 'ATP'
-- this row will reflect the net availability from ATP perspective
-- the cursor will select requirement from atp perspecive and
-- in calculate_cum we will calculate atp net
-------------------------------------------------------------------
/*
SELECT
crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
ATP_REQUIRED_HOURS,
decode(g_optimized_plan, 1, trunc(crp.start_date),
nvl(trunc(crp.end_date),trunc(crp.start_date))),
trunc(crp.end_date),
sum(decode( nvl(cpr.batchable_flag,2), 1 ,
s.new_order_quantity * decode(cpr.uom_class_type,1,
i.unit_weight, i.unit_volume)
* nvl(decode(crp.end_date,NULL, crp.resource_hours,
crp.daily_resource_hours),0),
decode(cpr.line_flag,
2, decode(crp.end_date,
NULL, crp.resource_hours,
crp.daily_resource_hours),
cpr.max_rate*crp.daily_resource_hours))),
to_number(null),
to_char(null)
FROM msc_resource_requirements crp,
msc_department_resources cpr,
msc_form_query list,
msc_supplies s,
msc_system_items i
WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
AND cpr.resource_id = list.number3
AND cpr.plan_id = g_designator
AND cpr.organization_id = list.number1
AND cpr.sr_instance_id = list.number4
AND crp.plan_id = cpr.plan_id
AND trunc(crp.start_date) <= trunc(g_cutoff_date)
AND crp.resource_id = cpr.resource_id
AND crp.department_id = cpr.department_id
AND crp.organization_id = cpr.organization_id
AND crp.sr_instance_id = cpr.sr_instance_id
AND NVL(crp.parent_id, g_optimized_plan) =
decode(g_optimized_plan, 1,1,2,2,1)
AND list.query_id = g_item_list_id
AND crp.supply_id = s.transaction_id
AND crp.assembly_item_id = i.inventory_item_id
AND crp.sr_instance_id = i.sr_instance_id
AND crp.organization_id = i.organization_id
AND crp.plan_id = i.plan_id
AND i.inventory_item_id = s.inventory_item_id
AND i.organization_id = s.organization_id
AND i.sr_instance_id = s.sr_instance_id
AND i.plan_id = s.plan_id
AND ((i.bom_item_type <> 1 and i.bom_item_type <> 2) OR
(i.atp_flag ='Y') OR
(i.bom_item_type in (1, 2) AND s.record_source = 2) )
GROUP BY crp.organization_id,
crp.sr_instance_id,
crp.assembly_item_id,
list.number2,
crp.resource_id,
trunc(crp.start_date),
trunc(crp.end_date)
union all
*/
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT list.number1,
list.number4,
list.number5,
list.number2,
list.number3,
PLANNED_ORDER,
to_date(1, 'J'),
to_date(1, 'J'),
0,
to_number(null),
to_char(null)
FROM msc_form_query list
WHERE list.query_id = g_item_list_id
ORDER BY 1,2,4,5,10,11,7,6;
SELECT
sm.to_organization_id,
sm.sr_instance_id,
to_number(null),
sm.from_organization_id,
sm.transaction_id,
WT_AVAILABLE_HOURS,
cal.calendar_date,
to_date(NULL),
NVL(sm.weight_capacity,0),
to_number(null),
to_char(NULL)
FROM msc_interorg_ship_methods sm,
msc_form_query list,
msc_trading_partners tp,
msc_calendar_dates cal
WHERE cal.calendar_date BETWEEN g_plan_start_date+1 AND g_cutoff_date
AND cal.exception_set_id = tp.calendar_exception_set_id
AND cal.calendar_code = tp.calendar_code
AND cal.sr_instance_id = tp.sr_instance_id
AND tp.sr_instance_id = sm.sr_instance_id
AND tp.sr_tp_id = sm.to_organization_id
AND sm.plan_id = g_designator
AND sm.to_organization_id = list.number1
AND sm.sr_instance_id = list.number4
AND sm.transaction_id = list.number3
AND list.query_id = g_item_list_id
UNION ALL
SELECT
sm.to_organization_id,
sm.sr_instance_id,
to_number(null),
sm.from_organization_id,
sm.transaction_id,
VL_AVAILABLE_HOURS,
cal.calendar_date,
to_date(NULL),
NVL(sm.volume_capacity,0)
, to_number(null),
to_char(NULL)
FROM msc_interorg_ship_methods sm,
msc_form_query list,
msc_trading_partners tp,
msc_calendar_dates cal
WHERE cal.calendar_date BETWEEN g_plan_start_date+1 AND g_cutoff_date
AND cal.exception_set_id = tp.calendar_exception_set_id
AND cal.calendar_code = tp.calendar_code
AND cal.sr_instance_id = tp.sr_instance_id
AND tp.sr_tp_id = sm.to_organization_id
AND tp.sr_instance_id = sm.sr_instance_id
AND sm.plan_id = g_designator
AND sm.to_organization_id = list.number1
AND sm.sr_instance_id = list.number4
AND sm.transaction_id = list.number3
AND list.query_id = g_item_list_id
UNION ALL
SELECT
sm.to_organization_id,
sm.sr_instance_id,
to_number(null),
sm.from_organization_id,
sm.transaction_id,
WT_REQUIRED_HOURS,
sup.new_wip_start_date,
to_date(NULL),
NVL(sup.weight_capacity_used,0)
, to_number(null),
to_char(NULL)
FROM msc_interorg_ship_methods sm,
msc_supplies sup,
msc_form_query list
WHERE sm.plan_id = g_designator
AND sm.plan_id = sup.plan_id
AND sm.to_organization_id = sup.organization_id
AND sm.from_organization_id = sup.source_organization_id
AND sm.ship_method = sup.ship_method
AND sm.sr_instance_id = sup.sr_instance_id
AND sm.to_organization_id = list.number1
AND sm.sr_instance_id = list.number4
AND sm.transaction_id = list.number3
AND list.query_id = g_item_list_id
UNION ALL
SELECT
sm.to_organization_id,
sm.sr_instance_id,
to_number(null),
sm.from_organization_id,
sm.transaction_id,
VL_REQUIRED_HOURS,
sup.new_wip_start_date,
to_date(NULL),
NVL(sup.volume_capacity_used,0)
, to_number(null),
to_char(NULL)
FROM msc_interorg_ship_methods sm,
msc_supplies sup,
msc_form_query list
WHERE sm.plan_id = g_designator
AND sm.plan_id = sup.plan_id
AND sm.to_organization_id = sup.organization_id
AND sm.from_organization_id = sup.source_organization_id
AND sm.ship_method = sup.ship_method
AND sm.sr_instance_id = sup.sr_instance_id
AND sm.to_organization_id = list.number1
AND sm.sr_instance_id = list.number4
AND sm.transaction_id = list.number3
AND list.query_id = g_item_list_id
ORDER BY 1,2,4,5,7,6;
SELECT nvl(mdr.aggregate_resource_flag,2)
from msc_department_resources mdr,
msc_form_query list
where mdr.plan_id = g_designator
AND mdr.sr_instance_id = list.number4
AND mdr.organization_id = list.number1
AND mdr.department_id = list.number2
AND mdr.resource_id = list.number3
AND list.query_id = g_item_list_id;
SELECT DECODE(g_designator, -1, trunc(sysdate), trunc(curr_start_date)) - 1,
DECODE(g_designator, -1, trunc(sysdate+365), trunc(curr_cutoff_date))
FROM msc_plans
WHERE plan_id = g_designator;
SELECT cal.calendar_date
FROM msc_trading_partners tp,
msc_calendar_dates cal
WHERE tp.sr_tp_id = g_org_id
AND tp.sr_instance_id = g_inst_id
AND tp.calendar_exception_set_id = cal.exception_set_id
AND tp.partner_type = 3
AND tp.calendar_code = cal.calendar_code
AND tp.sr_instance_id = cal.sr_instance_id
AND cal.calendar_date BETWEEN p_start_date AND p_end_date
ORDER BY cal.calendar_date;
SELECT msc_capacity_plans_s.nextval
INTO g_query_id
FROM dual;
SELECT calendar_code, calendar_exception_set_id
INTO g_calendar_code, g_exc_set_id
FROM msc_trading_partners
WHERE sr_tp_id = g_org_id
AND sr_instance_id = g_inst_id
AND partner_type = 3;
select mca.seq_num
from msc_calendar_dates mca,
msc_trading_partners mtp
where mtp.sr_tp_id = p_org_id
and mtp.sr_instance_id = p_inst_id
and mtp.partner_type = 3
and mca.calendar_code = mtp.calendar_code
and mca.exception_set_id = mtp.calendar_exception_set_id
and mca.calendar_date = trunc(p_date);
SELECT NVL(cst.standard_cost, 0)
FROM msc_system_items cst
WHERE cst.inventory_item_id = activity_rec.assembly_item_id
AND cst.organization_id = activity_rec.org_id
AND cst.sr_instance_id = activity_rec.instance_id
AND cst.plan_id = -1;
select nvl(Batchable_flag,2)
from msc_department_resources
where department_id = p_dept_id
and resource_id = p_res_id
and plan_id = g_designator
and organization_id = p_org_id
and sr_instance_id = p_inst_id;
SELECT DISTINCT NVL(dept_overhead_cost,0)
INTO v_overhead
FROM msc_department_resources
WHERE organization_id = p_org_id
AND sr_instance_id = p_inst_id
AND department_id = p_dept_id
AND plan_id = -1;
SELECT (1 + v_overhead)*NVL(res.resource_cost,0)
INTO v_res_cost
FROM msc_department_resources res
WHERE res.organization_id = p_org_id
AND res.sr_instance_id = p_inst_id
AND res.department_id = p_dept_id
AND res.resource_id = p_res_id
AND res.plan_id = -1;
SELECT dept_res.department_code,
dept_res.department_class,
dept_res.resource_code,
lkps.meaning,
dept_res.resource_group_name
INTO v_dept_code,
v_dept_class_code,
v_res_code,
v_resource_type_code,
v_res_grp_name
FROM mfg_lookups lkps,
msc_department_resources dept_res
WHERE lkps.lookup_type(+) = 'BOM_RESOURCE_TYPE'
AND lkps.lookup_code(+) = dept_res.resource_type
AND dept_res.plan_id = -1
AND dept_res.organization_id = p_org_id
AND dept_res.sr_instance_id = p_inst_id
AND dept_res.department_id = p_dept_id
AND dept_res.resource_id = p_res_id;
INSERT INTO msc_capacity_plans(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
sr_instance_id,
department_id,
resource_id,
department_name,
department_class,
resource_name,
resource_type,
resource_group_name, -- Will store the serial number
quantity36, -- Will store the res_instance_id
bucket_type,
bucket_date,
quantity1, quantity2, quantity3, quantity4,
quantity5, quantity6, quantity7, quantity8,
quantity9, quantity10, quantity11, quantity12,
quantity13, quantity14, quantity15, quantity16,
quantity17, quantity18, quantity19, quantity20,
quantity21)
VALUES (
g_query_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_org_id,
p_inst_id,
DECODE(g_current_data,3,-1,p_dept_id),
p_res_id,
v_dept_code,
v_dept_class_code,
v_res_code,
v_resource_type_code,
p_serial_number,
p_res_instance_id,
g_bucket_type,
g_dates(v_loop),
bucket_cells(v_loop+g_num_of_buckets*0), -- available hours
bucket_cells(v_loop+g_num_of_buckets*1), -- required hours
bucket_cells(v_loop+g_num_of_buckets*2), -- NET_AVAILABLE
bucket_cells(v_loop+g_num_of_buckets*3), -- CUM_AVAILABLE
bucket_cells(v_loop+g_num_of_buckets*4), -- UTILIZATION
bucket_cells(v_loop+g_num_of_buckets*5), -- CUM_UTILIZATION
bucket_cells(v_loop+g_num_of_buckets*6), -- DAILY_REQUIRED
bucket_cells(v_loop+g_num_of_buckets*7), -- DAILY_AVAILABLE
bucket_cells(v_loop+g_num_of_buckets*8), -- RESOURCE_COST
bucket_cells(v_loop+g_num_of_buckets*9), -- CAP_CHANGES
bucket_cells(v_loop+g_num_of_buckets*10),
bucket_cells(v_loop+g_num_of_buckets*11),
bucket_cells(v_loop+g_num_of_buckets*12),
bucket_cells(v_loop+g_num_of_buckets*13),
bucket_cells(v_loop+g_num_of_buckets*14),
bucket_cells(v_loop+g_num_of_buckets*15),
bucket_cells(v_loop+g_num_of_buckets*16),
bucket_cells(v_loop+g_num_of_buckets*17),
bucket_cells(v_loop+g_num_of_buckets*18),
bucket_cells(v_loop+g_num_of_buckets*19),
bucket_cells(v_loop+g_num_of_buckets*20) -- atp net
);
SELECT calendar_code, calendar_exception_set_id
INTO g_calendar_code, g_exc_set_id
FROM msc_trading_partners
WHERE sr_tp_id = activity_rec.org_id
AND sr_instance_id = activity_rec.instance_id
AND partner_type = 3;
select count(*) into g_daily_counts
from msc_plan_buckets
where PLAN_ID = g_designator
and SR_INSTANCE_ID = g_inst_id
and ORGANIZATION_ID = g_org_id
and BUCKET_TYPE = 1;
select plan_type into l_plan_type
from msc_plans
where plan_id = p_plan_id;
sql_stmt := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1, '|| -- org id
'number2, '|| -- dept id
'number3, '|| -- res id
'number4, '|| -- sr_instance
'number5, '|| -- owning dept id
'number7, '|| -- node_type
'number8, '|| -- RES INSTANCE ID
'char1, '||
'char2, '||
'char3) '|| -- SERIAL NUMBER
' SELECT DISTINCT '|| p_query_id || ', '||
'sysdate, '||
'1, '||
'sysdate, '||
'1, '||
'1, ';
''' ''' || -- insert an empty space, otherwise will error out in java
' FROM msc_interorg_ship_methods '||
'WHERE transaction_id in ('||p_res_list||') ' ||
'AND (sr_instance_id,to_organization_id) in ('||p_org_list||') ';