[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_WORK_DAY CONSTANT INTEGER := 1;
SELECT next_seq_num
FROM bom_calendar_dates
WHERE calendar_code = g_calendar_code
AND exception_set_id = g_exc_set_id
AND calendar_date = p_date;
SELECT prior_seq_num
FROM bom_calendar_dates
WHERE calendar_code = g_calendar_code
AND exception_set_id = g_exc_set_id
AND calendar_date = p_date;
SELECT
recom.organization_id,
recom.inventory_item_id,
list.number2,
routing.resource_id,
-1,
PLANNED_ORDER,
TRUNC(res_start.calendar_date),
to_date(NULL),
DECODE(routing.basis, BASIS_PER_ITEM,
GREATEST(routing.runtime_quantity* (nvl(recom.new_order_quantity,
recom.firm_quantity)-
NVL(recom.implemented_quantity,0) -
NVL(recom.quantity_in_process,0)),0),
DECODE(SIGN(nvl(recom.new_order_quantity, recom.firm_quantity) -
NVL(recom.implemented_quantity,0) -
NVL(recom.quantity_in_process,0)),
1, routing.runtime_quantity, 0))
FROM bom_calendar_dates res_start,
bom_calendar_dates order_date,
mrp_recommendations recom,
mrp_system_items items,
mrp_planned_resource_reqs routing,
mtl_parameters org,
mrp_form_query list
WHERE res_start.exception_set_id = org.calendar_exception_set_id
AND res_start.calendar_code = org.calendar_code
AND res_start.seq_num = order_date.prior_seq_num -
ceil((1 - NVL(routing.resource_offset_percent,0)) *
(NVL(items.fixed_lead_time,0) + NVL(items.variable_lead_time,0) *
(recom.new_order_quantity - NVL(recom.implemented_quantity,0) +
NVL(recom.quantity_in_process,0))))
AND TRUNC(res_start.calendar_date) < g_cutoff_date
AND order_date.exception_set_id = org.calendar_exception_set_id
AND order_date.calendar_code = org.calendar_code
AND order_date.calendar_date = recom.new_schedule_date
AND recom.order_type = M_PLANNED_ORDER
AND recom.compile_designator = items.compile_designator
AND recom.organization_id = items.organization_id
AND recom.inventory_item_id = items.inventory_item_id
AND items.organization_id = routing.organization_id
AND items.compile_designator = routing.compile_designator
AND items.inventory_item_id = routing.using_assembly_item_id
AND routing.alternate_routing_designator is NULL
AND routing.compile_designator = g_designator
AND routing.organization_id = org.organization_id
AND routing.department_id in (select department_id
from crp_planned_resources cpr
where cpr.compile_designator = g_designator
and cpr.organization_id = list.number1
and cpr.owning_department_id = list.number2)
AND routing.resource_id = list.number3
AND org.organization_id = list.number1
AND list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
-- ==================================
-- Discrete Job Dept/Res Requirement - tested
-- ==================================
SELECT
res.organization_id,
jobs.primary_item_id,
list.number2,
res.resource_id,
-1,
DECODE(jobs.job_type,
WIP_NONSTANDARD, NONSTD_JOBS, DISCRETE_JOBS),
NVL(res.start_date, jobs.scheduled_start_date),
DECODE(g_spread_load,
SYS_YES, res.completion_date, to_date(NULL)),
DECODE(res.basis_type, BASIS_PER_ITEM,
NVL((res.usage_rate_or_amount*(op.scheduled_quantity -
NVL(op.cumulative_scrap_quantity,0)-NVL(op.quantity_completed,0))
- res.applied_resource_units)*muc2.conversion_rate/
muc1.conversion_rate, 0),
DECODE(res.applied_resource_units, 0,
NVL(res.usage_rate_or_amount*muc2.conversion_rate/
muc1.conversion_rate, 0),
0))/compute_days_between (g_spread_load,res.start_date,res.completion_date)
FROM mtl_uom_conversions muc2,
wip_discrete_jobs jobs,
wip_operations op,
wip_operation_resources res,
mrp_form_query list,
mtl_uom_conversions muc1
WHERE muc1.inventory_item_id = 0
AND muc1.uom_code = g_hour_uom
AND muc2.uom_code = res.uom_code
AND muc2.inventory_item_id = 0
AND muc2.uom_class = muc1.uom_class
AND nvl(res.start_date,jobs.scheduled_start_date) 0
AND op.scheduled_quantity - NVL(op.quantity_completed, 0) > 0
AND op.wip_entity_id = res.wip_entity_id
AND op.operation_seq_num = res.operation_seq_num
AND op.organization_id = res.organization_id
AND op.department_id in (select department_id
from crp_planned_resources cpr
where cpr.compile_designator = g_designator
and cpr.organization_id = list.number1
and cpr.owning_department_id = list.number2)
AND res.wip_entity_id >= 0
AND res.organization_id = list.number1
AND res.resource_id = list.number3
AND list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
----------------------------------------
--- Flow Schedule Dept/Res Reqs - tested
----------------------------------------
SELECT
fs.organization_id,
fs.primary_item_id,
seqs.department_id,
res.resource_id,
-1,
DISCRETE_JOBS,
trunc(res_start.calendar_date),
to_date(NULL),
DECODE(res.basis_type, BASIS_PER_ITEM,
GREATEST( NVL(res.usage_rate_or_amount*muc2.conversion_rate/
muc1.conversion_rate, 0)*
(NVL(fs.planned_quantity,0) -
NVL(fs.quantity_completed,0)),0),
NVL(res.usage_rate_or_amount*muc2.conversion_rate/
muc1.conversion_rate, 0))
FROM bom_calendar_dates res_start,
bom_calendar_dates order_date,
mtl_system_items items,
mtl_parameters param,
wip_flow_schedules fs,
bom_operational_routings routing,
bom_operation_sequences seqs,
bom_operation_resources res,
bom_resources bom_res,
mtl_uom_conversions muc2,
mtl_uom_conversions muc1,
mrp_form_query list
WHERE res_start.exception_set_id = param.calendar_exception_set_id
AND res_start.calendar_code = param.calendar_code
AND res_start.seq_num = order_date.prior_seq_num -
ceil((1 - NVL(res.resource_offset_percent/100,0)) *
(NVL(items.fixed_lead_time,0) + NVL(items.variable_lead_time,0) *
(fs.planned_quantity - NVL(fs.quantity_completed,0))))
AND TRUNC(res_start.calendar_date) < g_cutoff_date
AND order_date.exception_set_id = param.calendar_exception_set_id
AND order_date.calendar_code = param.calendar_code
AND order_date.calendar_date = fs.scheduled_completion_date
AND param.organization_id = fs.organization_id
AND items.organization_id = fs.organization_id
AND items.inventory_item_id = fs.primary_item_id
AND nvl(fs.alternate_routing_designator, '-23453')
= nvl(routing.alternate_routing_designator, '-23453')
AND fs.organization_id = routing.organization_id
AND fs.primary_item_id = routing.assembly_item_id
AND fs.scheduled_completion_date >= TRUNC(sysdate)
AND routing.common_routing_sequence_id = seqs.routing_sequence_id
AND routing.organization_id = bom_res.organization_id
AND TRUNC(seqs.effectivity_date) <= TRUNC(g_dates(1))
AND nvl(seqs.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(g_dates(1))
AND nvl(seqs.operation_type, 1) = 1
AND muc2.inventory_item_id = 0
AND muc2.uom_class = muc1.uom_class
AND muc2.uom_code = bom_res.unit_of_measure
AND muc1.inventory_item_id = 0
AND muc1.uom_code = g_hour_uom
AND seqs.department_id = list.number2
AND seqs.operation_sequence_id = res.operation_sequence_id
AND res.resource_id = bom_res.resource_id
AND bom_res.organization_id = list.number1
AND bom_res.resource_id = list.number3
AND list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Repetitive Dept/Res Reqs - tested
-- ===================================
SELECT
res.organization_id,
items.primary_item_id,
op.department_id,
res.resource_id,
-1,
REPETITIVE,
NVL(res.start_date, rep.first_unit_start_date),
NVL(res.completion_date, rep.last_unit_completion_date),
DECODE(res.basis_type, BASIS_PER_ITEM,
NVL((res.usage_rate_or_amount*op.scheduled_quantity
- res.applied_resource_units)*muc2.conversion_rate/
muc1.conversion_rate, 0),
DECODE(res.applied_resource_units, 0,
NVL(res.usage_rate_or_amount*muc2.conversion_rate/
muc1.conversion_rate, 0),
0))
FROM mtl_uom_conversions muc2,
wip_repetitive_schedules rep,
wip_repetitive_items items,
wip_operations op,
wip_operation_resources res,
mrp_form_query list,
mtl_uom_conversions muc1
WHERE muc1.inventory_item_id = 0
AND muc1.uom_code = g_hour_uom
AND muc2.uom_code = res.uom_code
AND muc2.inventory_item_id = 0
AND muc2.uom_class = muc1.uom_class
AND items.wip_entity_id = rep.wip_entity_id
AND items.organization_id = rep.organization_id
AND items.line_id = rep.line_id
AND nvl(res.start_date,rep.first_unit_start_date) < g_cutoff_date
AND rep.status_type IN (JOB_RELEASED, JOB_UNRELEASED, JOB_COMPLETE,
JOB_HOLD)
AND rep.organization_id = op.organization_id
AND rep.repetitive_schedule_id = op.repetitive_schedule_id
AND nvl((op.scheduled_quantity * res.usage_rate_or_amount -
res.applied_resource_units),1) > 0
AND op.repetitive_schedule_id = res.repetitive_schedule_id
AND op.operation_seq_num = res.operation_seq_num
AND op.organization_id = res.organization_id
AND op.department_id = list.number2
AND res.wip_entity_id >= 0
AND res.organization_id = list.number1
AND res.resource_id = list.number3
AND list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Planned Order line Requirement
-- ===================================
SELECT
mr.organization_id,
mr.inventory_item_id,
-1,
-1,
mr.line_id,
PLANNED_ORDER,
trunc(mr.new_wip_start_date),
to_date(null),
(mr.new_order_quantity - NVL(mr.implemented_quantity,0) +
NVL(mr.quantity_in_process,0))
FROM mrp_recommendations mr,
mrp_form_query list
WHERE mr.order_type = M_PLANNED_ORDER
AND mr.new_wip_start_date < g_cutoff_date
AND mr.compile_designator = g_designator
AND mr.line_id = list.number2
AND mr.organization_id = list.number1
AND list.number3 is null
AND list.query_id = g_item_list_id
UNION ALL
-- ==================================
-- Discrete Job line Requirement - tested
-- ==================================
SELECT
jobs.organization_id,
jobs.primary_item_id,
-1,
-1,
jobs.line_id,
DECODE(jobs.job_type,
WIP_NONSTANDARD, NONSTD_JOBS,
WIP_DISCRETE, DISCRETE_JOBS),
jobs.scheduled_start_date,
to_date(NULL),
SUM(GREATEST( 0, (jobs.net_quantity - jobs.quantity_completed
- jobs.quantity_scrapped)))
FROM wip_discrete_jobs jobs,
mrp_form_query list
WHERE jobs.scheduled_start_date 0
AND jobs.wip_entity_id >= 0
AND jobs.organization_id = list.number1
AND jobs.line_id = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
GROUP BY
jobs.organization_id,
jobs.primary_item_id,
jobs.line_id,
jobs.scheduled_start_date,
jobs.job_type
UNION ALL
-- =====================================
-- Flow Schedules line requirement
-- =====================================
SELECT
fs.organization_id,
fs.primary_item_id,
-1,
-1,
fs.line_id,
DISCRETE_JOBS,
fs.scheduled_start_date,
to_date(NULL),
SUM(GREATEST( 0, (fs.planned_quantity - fs.quantity_completed)))
FROM wip_flow_schedules fs,
mrp_form_query list
WHERE fs.scheduled_start_date = TRUNC(sysdate)
AND fs.wip_entity_id >= 0
AND fs.organization_id = list.number1
AND fs.line_id = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
GROUP BY
fs.organization_id,
fs.primary_item_id,
fs.line_id,
fs.scheduled_start_date
UNION ALL
-- =====================================
-- Repetitive Schedules line requirement - tested
-- =====================================
SELECT
sched.organization_id,
rep_items.primary_item_id,
-1,
-1,
sched.line_id,
REPETITIVE,
TRUNC(sched.first_unit_start_date),
TRUNC(sched.last_unit_start_date),
sched.daily_production_rate*lines.maximum_rate/
rep_items.PRODUCTION_LINE_RATE
FROM wip_repetitive_items rep_items,
wip_repetitive_schedules sched,
wip_lines lines,
mrp_form_query list
WHERE rep_items.organization_id = sched.organization_id
AND rep_items.line_id = sched.line_id
AND rep_items.wip_entity_id = sched.wip_entity_id
AND TRUNC(sched.first_unit_start_date) < g_cutoff_date
AND TRUNC(sched.last_unit_start_date) >= g_dates(1)
AND sched.status_type IN (JOB_UNRELEASED,
JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
AND sched.organization_id = lines.organization_id
AND sched.line_id = lines.line_id
AND lines.organization_id = list.number1
AND lines.line_id = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
-- =========================
-- Line availability
-- =========================
UNION ALL
SELECT
line.organization_id,
TO_NUMBER(NULL),
-1,
-1,
line.line_id,
AVAILABLE_HOURS,
g_dates(1),
trunc(g_cutoff_date-1),
nvl(line.maximum_rate, 0) *
(decode(least(line.start_time, line.stop_time),
line.stop_time, (line.stop_time + (24 *3600)),
line.stop_time) - line.start_time) / 3600
FROM wip_lines line,
mrp_form_query list
WHERE nvl(line.disable_date, sysdate + 1) > sysdate
AND line.organization_id = list.number1
AND line.line_id = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
-- ============================
-- Dept/Resource Availability
-- ============================
UNION ALL
SELECT
param.organization_id,
TO_NUMBER(NULL),
dept_res.department_id,
dept_res.resource_id,
-1,
AVAILABLE_HOURS,
trunc(cal.calendar_date),
trunc(cal.calendar_date),
SUM(DECODE(dept_res.available_24_hours_flag,
1, dept_res.capacity_units * 24 *
NVL(dept_res.utilization, 1.0) *
NVL(dept_res.efficiency, 1.0),
(DECODE(LEAST(shifts.to_time, shifts.from_time),
shifts.to_time, shifts.to_time + 24*3600,
shifts.to_time) - shifts.from_time) *
(dept_res.capacity_units/3600) *
NVL(dept_res.utilization, 1.0) *
NVL(dept_res.efficiency, 1.0) ))
FROM bom_shift_times shifts,
bom_resource_shifts res_shifts,
bom_department_resources dept_res,
mtl_parameters param,
bom_calendar_dates cal,
mrp_form_query list
WHERE (shifts.calendar_code is NULL OR
shifts.calendar_code = param.calendar_code)
AND shifts.shift_num (+) = res_shifts.shift_num
AND res_shifts.resource_id (+)= dept_res.resource_id
AND res_shifts.department_id (+)= dept_res.department_id
AND dept_res.share_from_dept_id is NULL
AND cal.calendar_code = param.calendar_code
AND cal.exception_set_id = param.calendar_exception_set_id
AND trunc(cal.calendar_date) >= trunc(sysdate)
AND ((cal.seq_num is not null
and not exists ( select 1 from CRP_CAL_SHIFT_DELTA delta1
where delta1.calendar_code =
shifts.calendar_code
and delta1.exception_set_id =
param.calendar_exception_set_id
and delta1.delta_code = 1
and delta1.calendar_date = cal.calendar_date
and delta1.shift_num = shifts.shift_num))
OR
(cal.seq_num is null
and exists ( select 1 from CRP_CAL_SHIFT_DELTA delta1
where delta1.calendar_code =
shifts.calendar_code
and delta1.exception_set_id =
param.calendar_exception_set_id
and delta1.delta_code = 2
and delta1.calendar_date = cal.calendar_date
and delta1.shift_num = shifts.shift_num)))
AND dept_res.department_id = list.number2
AND dept_res.resource_id = list.number3
AND param.organization_id = list.number1
AND list.number3 is not null
AND list.query_id = g_item_list_id
GROUP BY
param.organization_id,
dept_res.department_id,
dept_res.resource_id,
trunc(cal.calendar_date)
-- ================================
-- Dept/Resource modif/add workday
-- ================================
UNION ALL
SELECT
orgs.planned_organization,/*2681093*/
TO_NUMBER(NULL),
brc.department_id,
brc.resource_id,
-1,
CAP_CHANGES,
dates.calendar_date,
dates.calendar_date,
(brc.capacity_change *
(DECODE(LEAST(NVL(brc.from_time, 0), NVL(brc.to_time, 1)),
NVL(brc.to_time, 1), 24 * 3600 + NVL(brc.to_time, 1),
NVL(brc.to_time, 1)) - NVL(brc.from_TIME, 0)))/3600
FROM bom_calendar_dates dates,
bom_resource_changes brc,
mrp_plan_organizations_v orgs,
mtl_parameters param,
mrp_form_query list
WHERE dates.exception_set_id = param.calendar_exception_set_id
AND dates.calendar_code = param.calendar_code
AND (brc.action_type = ADD_WORK_DAY
OR dates.seq_num is not NULL
OR (dates.seq_num IS NULL
and exists ( select 1
from CRP_CAL_SHIFT_DELTA delta1
where delta1.calendar_code =
dates.calendar_code
and delta1.exception_set_id =
dates.exception_set_id
and delta1.delta_code = 2
and delta1.calendar_date =
dates.calendar_date
and delta1.shift_num = brc.shift_num)))
AND dates.calendar_date BETWEEN TRUNC(brc.from_date)
AND TRUNC(NVL(brc.to_date, brc.from_date))
AND TRUNC(brc.from_date) < g_cutoff_date
AND TRUNC(NVL(brc.to_date, brc.from_date)) >= g_dates(1)
AND brc.simulation_set = orgs.simulation_set
AND brc.department_id = list.number2
AND brc.resource_id = list.number3
AND brc.action_type in (ADD_WORK_DAY, MODIFY_WORK_DAY)
AND orgs.compile_designator = g_designator
AND orgs.planned_organization = param.organization_id
AND orgs.organization_id = g_org_id
AND param.organization_id = list.number1
AND list.number3 is not null
AND list.query_id = g_item_list_id
AND not exists
(SELECT 'Exists'
FROM bom_resource_changes brc2
WHERE brc2.department_id = brc.department_id
AND brc2.resource_id = brc.resource_id
AND brc2.shift_num = brc.shift_num
AND brc2.action_type = DELETE_WORK_DAY
AND brc2.to_date = dates.calendar_date
AND brc2.from_date is null
AND brc2.simulation_set = brc.simulation_set)
-- ===============================================================
-- Resource delete work days
-- Note, we don't have capacity modifications for
-- borrowed resources. Therefore, the following query will not
-- return any rows for borrowed resources in a department
-- ===============================================================
UNION ALL
SELECT
orgs.planned_organization,/*2681093*/
TO_NUMBER(NULL),
brc.department_id,
brc.resource_id,
-1,
CAP_CHANGES,
brc.from_date,
brc.from_date,
-1 * SUM((DECODE(LEAST(shifts.to_time, shifts.from_time),
shifts.to_time, shifts.to_time + 24*3600,
shifts.to_time) - shifts.from_time) *
dept_res.capacity_units / 3600)
FROM bom_shift_times shifts,
bom_resource_changes brc,
bom_department_resources dept_res,
mrp_plan_organizations_v orgs,
mtl_parameters param,
mrp_form_query list
WHERE shifts.calendar_code = param.calendar_code
AND shifts.shift_num = brc.shift_num
AND TRUNC(brc.from_date) < g_cutoff_date
AND TRUNC(brc.from_date) >= g_dates(1)
AND brc.action_type = DELETE_WORK_DAY
AND brc.simulation_set = orgs.simulation_set
AND brc.department_id = dept_res.department_id
AND brc.resource_id = dept_res.resource_id
AND dept_res.department_id = list.number2
AND dept_res.resource_id = list.number3
AND orgs.compile_designator = g_designator
AND orgs.planned_organization = param.organization_id
AND orgs.organization_id = g_org_id
AND param.organization_id = list.number1
AND list.number3 is not null
AND list.query_id = g_item_list_id
GROUP BY
orgs.planned_organization, /*2681093*/
brc.department_id,
brc.resource_id,
brc.from_date,
brc.from_date
ORDER BY 1,3,4,5,7,6;
SELECT
crp.organization_id,
crp.assembly_item_id,
cpr.owning_department_id,
crp.resource_id,
-1,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER,
M_REPETITIVE, REPETITIVE,
M_DISCRETE_JOBS, DISCRETE_JOBS,
M_NONSTD_JOBS, NONSTD_JOBS,
M_FLOW_SCHEDULES, DISCRETE_JOBS),
trunc(crp.resource_date),
trunc(crp.resource_end_date),
decode(crp.resource_end_date,
NULL, crp.resource_hours,
crp.daily_resource_hours)
FROM
crp_resource_plan crp,
crp_planned_resources cpr,
mrp_form_query list
WHERE
cpr.owning_department_id = list.number2
AND cpr.resource_id = list.number3
AND cpr.compile_designator = g_designator
AND cpr.organization_id = list.number1
AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES)
AND crp.designator = cpr.compile_designator
AND crp.resource_date < 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 list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
-- ============================================
-- Discrete job/Planned order Line Requirement
-- ============================================
SELECT /*+ ORDERED
USE_NL(mr)
INDEX(list MRP_FORM_QUERY_N1)
INDEX(mr MRP_RECOMMENDATIONS_N2) */
mr.organization_id,
mr.inventory_item_id,
-1,
-1,
mr.line_id,
decode(mr.order_type,
M_PLANNED_ORDER, PLANNED_ORDER,
M_DISCRETE_JOBS, DISCRETE_JOBS,
M_NONSTD_JOBS, NONSTD_JOBS,
M_FLOW_SCHEDULES, DISCRETE_JOBS),
NVL(mr.new_wip_start_date,mr.new_schedule_date),
to_date(null),
mr.new_order_quantity
FROM mrp_form_query list,
mrp_recommendations mr
WHERE mr.order_type in (M_PLANNED_ORDER, M_DISCRETE_JOBS,
M_NONSTD_JOBS, M_FLOW_SCHEDULES)
AND mr.disposition_status_type <> 2
AND mr.new_schedule_date < g_cutoff_date
AND mr.compile_designator = g_designator
AND mr.line_id = list.number2
AND mr.organization_id = list.number1
AND list.number3 is null
AND list.query_id = g_item_list_id
UNION ALL
-- ============================================
-- RCCP Planned order Line Requirement
-- Note, line_id is stored in department_id
-- and resource_id is set to -1
-- ============================================
SELECT
crp.organization_id,
crp.source_item_id,
-1,
-1,
crp.department_id,
decode(crp.supply_type,
M_PLANNED_ORDER, PLANNED_ORDER,
M_REPETITIVE, REPETITIVE),
trunc(crp.resource_date),
to_date(NULL),
crp.load_rate
FROM crp_resource_plan crp,
mrp_form_query list
WHERE crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE)
AND crp.designator = g_designator
AND crp.resource_date < g_cutoff_date
AND crp.resource_id = -1
AND crp.department_id = list.number2
AND crp.organization_id = list.number1
AND list.number3 is null
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Repetitive schedule line requirements
-- ===================================
SELECT /*+ORDERED
INDEX(list MRP_FORM_QUERY_N1)
INDEX(msrs MRP_SUGG_REP_SCHEDULES_N3) */
msrs.organization_id,
msrs.inventory_item_id,
-1,
-1,
msrs.repetitive_line,
REPETITIVE,
trunc(msrs.first_unit_start_date),
trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
msrs.load_factor_rate
FROM mrp_form_query list,
mrp_sugg_rep_schedules msrs
WHERE msrs.first_unit_start_date < g_cutoff_date
AND msrs.compile_designator = g_designator
AND msrs.organization_id = list.number1
AND msrs.repetitive_line = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Availability for Dept/Res
-- ===================================
SELECT
avail.organization_id,
TO_NUMBER(NULL),
NVL(avail.department_id,-1),
NVL(avail.resource_id,-1),
NVL(avail.line_id,-1),
AVAILABLE_HOURS,
trunc(avail.resource_start_date),
trunc(least(nvl(avail.resource_end_date,g_cutoff_date), g_cutoff_date-1)),
avail.resource_units * avail.resource_hours
FROM crp_available_resources avail,
mrp_form_query list
WHERE avail.resource_start_date < g_cutoff_date
AND nvl(avail.resource_end_date, g_cutoff_date) >= g_dates(1)
AND avail.compile_designator = g_designator
AND avail.organization_id = list.number1
AND avail.department_id = list.number2
AND avail.resource_id = list.number3
AND list.number3 is not null
AND list.query_id = g_item_list_id
UNION ALL
-- ===================================
-- Availability for Lines
-- ===================================
SELECT
avail.organization_id,
TO_NUMBER(NULL),
NVL(avail.department_id,-1),
NVL(avail.resource_id,-1),
NVL(avail.line_id,-1),
AVAILABLE_HOURS,
trunc(avail.resource_start_date),
trunc(least(nvl(avail.resource_end_date,g_cutoff_date),g_cutoff_date-1)),
avail.max_rate
FROM crp_available_resources avail,
mrp_form_query list
WHERE avail.resource_start_date < g_cutoff_date
AND nvl(avail.resource_end_date, g_cutoff_date) >= g_dates(1)
AND avail.compile_designator = g_designator
AND avail.organization_id = list.number1
AND avail.line_id = list.number2
AND list.number3 is null
AND list.query_id = g_item_list_id
ORDER BY
1,3,4,5,7,6;
SELECT crp_form_query_s.nextval
INTO g_query_id
FROM dual;
SELECT calendar_code, calendar_exception_set_id
INTO g_calendar_code, g_exc_set_id
FROM mtl_parameters
WHERE organization_id = g_planned_org;
SELECT trunc(plan_start_date)
INTO g_plan_start_date
FROM mrp_plans
WHERE compile_designator = g_designator
AND organization_id = g_org_id;
SELECT
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,
date37
INTO
g_dates(1), g_dates(2), g_dates(3), g_dates(4),
g_dates(5), g_dates(6), g_dates(7), g_dates(8),
g_dates(9), g_dates(10), g_dates(11), g_dates(12),
g_dates(13), g_dates(14), g_dates(15), g_dates(16),
g_dates(17), g_dates(18), g_dates(19), g_dates(20),
g_dates(21), g_dates(22), g_dates(23), g_dates(24),
g_dates(25), g_dates(26), g_dates(27), g_dates(28),
g_dates(29), g_dates(30), g_dates(31), g_dates(32),
g_dates(33), g_dates(34), g_dates(35), g_dates(36),
g_dates(37)
FROM mrp_workbench_bucket_dates
WHERE organization_id = g_org_id
AND nvl(planned_organization,organization_id) =
g_planned_org
AND compile_designator = g_designator
AND bucket_type = DECODE(g_current_data,
SYS_YES, DECODE(g_bucket_type,
1, -1,
2, -2,
3, -3), g_bucket_type);
SELECT NVL(cst.item_cost, 0)
INTO v_res_cost
FROM cst_item_costs cst,
mtl_parameters org
WHERE cst.cost_type_id(+) = org.primary_cost_method
AND cst.organization_id(+) = org.cost_organization_id
AND cst.inventory_item_id(+) = activity_rec.assembly_item_id
AND org.organization_id = activity_rec.org_id;
SELECT calendar_code, calendar_exception_set_id
INTO g_calendar_code, g_exc_set_id
FROM mtl_parameters
WHERE organization_id = g_planned_org;
SELECT SUM(NVL(rate_or_amount,0))
INTO v_overhead
FROM cst_department_overheads
WHERE organization_id = p_org_id
AND department_id = p_dept_id
AND cost_type_id = CST_FROZEN;
SELECT (1 + v_overhead)*NVL(res.resource_rate,0)
INTO v_res_cost
FROM cst_resource_costs res
WHERE res.organization_id = p_org_id
AND res.resource_id = p_res_id
AND res.cost_type_id = CST_FROZEN;
SELECT NVL(res.resource_rate,0)
INTO v_res_cost
FROM cst_resource_costs res
WHERE res.organization_id = p_org_id
AND res.resource_id = p_res_id
AND res.cost_type_id = CST_FROZEN;
SELECT SUM(decode(basis_type,BASIS_RESOURCE_VALUE, v_res_cost * NVL(rate_or_amount,0),
NVL(rate_or_amount,0)))
INTO v_overhead
FROM cst_department_overheads
WHERE organization_id = p_org_id
AND department_id = p_dept_id
AND cost_type_id = CST_FROZEN
AND basis_type in (BASIS_RESOURCE_VALUE, BASIS_RESOURCE_UNITS)
AND overhead_id IN (SELECT overhead_id
FROM cst_resource_overheads res
WHERE res.organization_id = p_org_id
AND res.resource_id = p_res_id
AND cost_type_id = CST_FROZEN);
SELECT dept.department_code,
dept.department_class_code,
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,
bom_resources res,
bom_department_resources dept_res,
bom_departments dept
WHERE lkps.lookup_type(+) = 'BOM_RESOURCE_TYPE'
AND lkps.lookup_code(+) = res.resource_type
AND res.resource_id = p_res_id
AND dept_res.department_id = p_dept_id
AND dept_res.resource_id = p_res_id
AND dept.department_id = p_dept_id;
SELECT line.line_code
INTO v_line_code
FROM wip_lines line
WHERE line.line_id = p_line_id;
INSERT INTO crp_capacity_plans(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
department_id,
resource_id,
line_id,
type_id,
department_name,
department_class,
resource_name,
resource_type,
resource_group_name,
line_name,
period1, period2, period3, period4,
period5, period6, period7, period8,
period9, period10, period11, period12,
period13, period14, period15, period16,
period17, period18, period19, period20,
period21, period22, period23, period24,
period25, period26, period27, period28,
period29, period30, period31, period32,
period33, period34, period35, period36)
VALUES (
g_query_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_org_id,
p_dept_id,
p_res_id,
p_line_id,
v_loop,
NVL(v_dept_code, v_line_code),
v_dept_class_code,
v_res_code,
v_resource_type_code,
v_res_grp_name,
v_line_code,
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+1),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+2),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+3),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+4),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+5),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+6),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+7),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+8),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+9),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+10),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+11),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+12),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+13),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+14),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+15),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+16),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+17),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+18),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+19),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+20),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+21),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+22),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+23),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+24),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+25),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+26),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+27),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+28),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+29),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+30),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+31),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+32),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+33),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+34),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+35),
bucket_cells((v_loop-1)*NUM_OF_COLUMNS+36));
SELECT calendar_code, calendar_exception_set_id
INTO g_calendar_code, g_exc_set_id
FROM mtl_parameters
WHERE organization_id = activity_rec.org_id;