[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mtp.organization_code
||':'||mdr.department_code || ':' || mdr.resource_code
from msc_department_resources mdr,
msc_trading_partners mtp
where mdr.department_id = v_dept_id
and mdr.resource_id = v_res_id
and mdr.plan_id = p_plan_id
and mdr.organization_id = v_org_id
and mdr.sr_instance_id = v_instance_id
and mtp.partner_type =3
and mtp.sr_tp_id = mdr.organization_id
and mtp.sr_instance_id = mdr.sr_instance_id;
SELECT 'EXISTS'
FROM msc_exception_details
WHERE number1 = p_transaction_id
AND sr_instance_id = p_instance_id
AND plan_id = p_plan_id
and exception_type =36
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND department_id = p_department_id
AND resource_id = p_resource_id;
select to_char(
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)),
format_mask) start_date,
to_char(least(g_cutoff_date,
nvl(
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date)),mrr.start_date)),
format_mask) end_date,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id)
||'/'||
msi.item_name
||'('||mrr.operation_seq_num||':'||mrr.resource_seq_num
||')' job_name,
mrr.transaction_id,
nvl(mrr.status,0) status,
nvl(mrr.applied,0) applied,
mfg.meaning supply_type,
mrr.sr_instance_id,
nvl(mrr.firm_flag,0) res_firm_flag,
ms.firm_planned_type sup_firm_flag,
decode(sign(ms.new_schedule_date - (ms.need_by_date+1)), 1,
1,0) late_flag,
mrr.supply_id
from msc_resource_requirements mrr,
msc_supplies ms,
msc_items msi,
mfg_lookups mfg
where mrr.organization_id =v_org_id
and mrr.sr_instance_id = v_instance_id
and mrr.department_id = v_dept_id
and mrr.resource_id = v_res_id
and mrr.plan_id = p_plan_id
and mrr.end_date is not null
and ms.inventory_item_id = msi.inventory_item_id
and mfg.lookup_type = 'MRP_ORDER_TYPE'
and mfg.lookup_code = ms.order_type
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
order by mrr.batch_number, nvl(mrr.firm_start_date, mrr.start_date);
select to_char(
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)),
format_mask) start_date,
to_char(least(g_cutoff_date,
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date))),
format_mask) end_date,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id)
||'/'||
msi.item_name
||'('||mrr.operation_seq_num||':'||mrr.resource_seq_num
||')' job_name,
mrr.transaction_id,
nvl(mrr.status,0) status,
nvl(mrr.applied,0) applied,
mfg.meaning supply_type,
mrr.sr_instance_id,
nvl(mrr.firm_flag,0) res_firm_flag,
ms.firm_planned_type sup_firm_flag,
decode(sign(ms.new_schedule_date - (ms.need_by_date+1)), 1,
1,0) late_flag,
mrr.supply_id
from msc_resource_requirements mrr,
msc_supplies ms,
msc_items msi,
mfg_lookups mfg,
msc_form_query mfq
where mrr.organization_id =v_org_id
and mrr.sr_instance_id = v_instance_id
and mrr.department_id = v_dept_id
and mrr.resource_id = v_res_id
and mrr.plan_id = p_plan_id
and mrr.end_date is not null
and ms.inventory_item_id = msi.inventory_item_id
and mfg.lookup_type = 'MRP_ORDER_TYPE'
and mfg.lookup_code = ms.order_type
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and mfq.number6 = mrr.transaction_id
and mfq.query_id = g_find_query_id
order by mrr.batch_number, nvl(mrr.firm_start_date, mrr.start_date);
g_block_start_item.delete;
g_block_start_row.delete;
select max_rate
from msc_department_resources
where organization_id =v_org_id
and sr_instance_id = v_instance_id
and department_id = v_dept_id
and resource_id = v_res_id
and plan_id = -1;
select 1
from msc_net_resource_avail
where organization_id =v_org_id
and sr_instance_id = v_instance_id
and department_id = v_dept_id
and resource_id = v_res_id
and plan_id = p_plan_id
and nvl(parent_id, 0) <> -1;
select nvl(MIN_CUTOFF_BUCKET,0)+nvl(HOUR_CUTOFF_BUCKET,0)+data_start_date,
nvl(MIN_CUTOFF_BUCKET,0)+data_start_date
into p_day_bkt_start_date,
p_hour_bkt_start_date
from msc_plans
where plan_id = p_plan_id;
oneAssignRecord.delete;
select start_date, end_date, assigned_units, over_cap, batch_number
BULK COLLECT INTO v_req_start, v_req_end, v_req_qty, v_over_cap, v_batch
FROM (
select -- req has been moved will use parent_id =2
decode(nvl(firm_flag,0),
NO_FIRM, start_date,
FIRM_RESOURCE, start_date,
FIRM_END,
firm_end_date - (end_date - start_date),
FIRM_END_RES,
firm_end_date - (end_date - start_date),
nvl(firm_start_date, start_date)) start_date,
least(g_cutoff_date,
decode(nvl(firm_flag,0),
NO_FIRM, end_date,
FIRM_RESOURCE, end_date,
FIRM_START,
firm_start_date + (end_date - start_date),
FIRM_START_RES,
firm_start_date + (end_date - start_date),
nvl(firm_end_date, end_date))) end_date,
assigned_units,
nvl(overloaded_capacity,0) over_cap,
batch_number
from msc_resource_requirements
where organization_id =v_org_id
and sr_instance_id = v_instance_id
and department_id = v_dept_id
and resource_id = v_res_id
and plan_id = p_plan_id
and end_date is not null
and batch_number is null
and nvl(parent_id,2) =2
and status = 0
and applied = 2
and nvl(firm_start_date,start_date) <= g_cutoff_date
and ( decode(nvl(firm_flag,0),
NO_FIRM, start_date,
FIRM_RESOURCE, start_date,
FIRM_END,
firm_end_date - (end_date - start_date),
FIRM_END_RES,
firm_end_date - (end_date - start_date),
nvl(firm_start_date, start_date)) <=
to_date(p_end,format_mask)
and decode(nvl(firm_flag,0),
NO_FIRM, end_date,
FIRM_RESOURCE, end_date,
FIRM_START,
firm_start_date + (end_date - start_date),
FIRM_START_RES,
firm_start_date + (end_date - start_date),
nvl(firm_end_date, end_date)) >=
to_date(p_start,format_mask) )
UNION ALL
select -- req has not been moved will use parent_id =1
mrr2.start_date,
least(g_cutoff_date,
decode(sign(mrr2.end_date-mrr2.start_date), 1,
mrr2.end_date, trunc(mrr2.start_date)+1
)
) end_date,
mrr2.resource_hours assigned_units,
-1 over_cap,
mrr2.batch_number
from msc_resource_requirements mrr,
msc_resource_requirements mrr2
where mrr.organization_id =v_org_id
and mrr.sr_instance_id = v_instance_id
and mrr.department_id = v_dept_id
and mrr.resource_id = v_res_id
and mrr.plan_id = p_plan_id
and mrr.batch_number is null
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and (nvl(mrr.status,1) <> 0 or nvl(mrr.applied,1) <> 2)
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
and ( decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)) <=
to_date(p_end,format_mask)
and decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date)) >=
to_date(p_start,format_mask) )
and mrr2.parent_id =1
and mrr2.organization_id =mrr.organization_id
and mrr2.sr_instance_id = mrr.sr_instance_id
and mrr2.department_id = mrr.department_id
and mrr2.resource_id = mrr.resource_id
and mrr2.plan_id = mrr.plan_id
and mrr2.supply_id = mrr.supply_id
and mrr2.resource_hours > 0
and mrr2.operation_seq_num = mrr.operation_seq_num
and mrr2.resource_seq_num = mrr.resource_seq_num
and mrr2.end_date is not null
UNION ALL
select -- batch resource from parent_id = 1
min(mrr2.start_date) start_date,
max(least(g_cutoff_date,
decode(sign(mrr2.end_date-mrr2.start_date), 1,
mrr2.end_date, trunc(mrr2.start_date)+1
)
)) end_date,
max(mrr2.resource_hours) assigned_units,
-1 over_cap,
mrr2.batch_number
from msc_resource_requirements mrr,
msc_resource_requirements mrr2
where mrr.organization_id =v_org_id
and mrr.sr_instance_id = v_instance_id
and mrr.department_id = v_dept_id
and mrr.resource_id = v_res_id
and mrr.plan_id = p_plan_id
and mrr.batch_number is not null
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and (nvl(mrr.status,1) <> 0 or nvl(mrr.applied,1) <> 2)
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
and ( decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)) <=
to_date(p_end,format_mask)
and decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date)) >=
to_date(p_start,format_mask) )
and mrr2.parent_id =1
and mrr2.organization_id =mrr.organization_id
and mrr2.sr_instance_id = mrr.sr_instance_id
and mrr2.department_id = mrr.department_id
and mrr2.resource_id = mrr.resource_id
and mrr2.plan_id = mrr.plan_id
and mrr2.supply_id = mrr.supply_id
and mrr2.resource_hours > 0
and mrr2.operation_seq_num = mrr.operation_seq_num
and mrr2.resource_seq_num = mrr.resource_seq_num
and mrr2.end_date is not null
group by mrr2.batch_number
UNION ALL
select -- batch resource from parent_id = 2
min(
decode(nvl(firm_flag,0),
NO_FIRM, start_date,
FIRM_RESOURCE, start_date,
FIRM_END,
firm_end_date - (end_date - start_date),
FIRM_END_RES,
firm_end_date - (end_date - start_date),
nvl(firm_start_date, start_date))) start_date,
max(least(g_cutoff_date,
decode(nvl(firm_flag,0),
NO_FIRM, end_date,
FIRM_RESOURCE, end_date,
FIRM_START,
firm_start_date + (end_date - start_date),
FIRM_START_RES,
firm_start_date + (end_date - start_date),
nvl(firm_end_date, end_date)))) end_date,
max(assigned_units) assigned_units,
max(nvl(overloaded_capacity,0)) over_cap
, batch_number
from msc_resource_requirements
where organization_id =v_org_id
and sr_instance_id = v_instance_id
and department_id = v_dept_id
and resource_id = v_res_id
and plan_id = p_plan_id
and batch_number is not null
and end_date is not null
and nvl(parent_id,2) =2
and status =0
and applied =2
and nvl(firm_start_date,start_date) <= g_cutoff_date
and ( decode(nvl(firm_flag,0),
NO_FIRM, start_date,
FIRM_RESOURCE, start_date,
FIRM_END,
firm_end_date - (end_date - start_date),
FIRM_END_RES,
firm_end_date - (end_date - start_date),
nvl(firm_start_date, start_date)) <=
to_date(p_end,format_mask)
and decode(nvl(firm_flag,0),
NO_FIRM, end_date,
FIRM_RESOURCE, end_date,
FIRM_START,
firm_start_date + (end_date - start_date),
FIRM_START_RES,
firm_start_date + (end_date - start_date),
nvl(firm_end_date, end_date)) >=
to_date(p_start,format_mask))
group by batch_number)
order by start_date
;
select
shift_date+from_time/86400,
decode(sign(to_time-from_time), 1,
shift_date+to_time/86400,
shift_date+1+to_time/86400),
capacity_units
bulk collect into v_avail_start, v_avail_end, v_avail_qty
from msc_net_resource_avail mrr
where organization_id =v_org_id
and sr_instance_id = v_instance_id
and department_id = v_dept_id
and resource_id = v_res_id
and plan_id = p_plan_id
and nvl(parent_id,0) <> -1
and capacity_units > 0
and (shift_date+from_time/86400) <=
to_date(p_end,format_mask)
and decode(sign(to_time-from_time), 1,
shift_date+to_time/86400,
shift_date+1+to_time/86400) >=
to_date(p_start,format_mask)
and shift_date <= g_cutoff_date
order by shift_date, from_time, to_time;
select mpb.bkt_start_date,mpb.bkt_end_date
BULK COLLECT INTO v_bkt_start, v_bkt_end
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and ( mpb.bkt_start_date between to_date(p_start,format_mask) and
to_date(p_end,format_mask)
or
mpb.bkt_end_date between to_date(p_start,format_mask) and
to_date(p_end,format_mask) )
and mpb.bkt_start_date >= p_day_bkt_start_date
order by 1;
Select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors
where mors.plan_id = p_plan_id
and mors.routing_sequence_id = l_rout_seq
and mors.operation_sequence_id = l_op_seq
and mors.sr_instance_id = p_instance_id
and mors.activity_group_id = l_act_group
;
SELECT distinct mrr.transaction_id,mor.principal_flag
FROM msc_resource_requirements mrr,
msc_operation_resources mor
WHERE mrr.plan_id = p_plan_id
AND mrr.sr_instance_id = p_instance_id
and mrr.routing_sequence_id = l_rout_seq
AND mrr.operation_sequence_id = l_op_seq
AND mrr.resource_seq_num = l_res_seq
and mor.plan_id = p_plan_id
and mor.sr_instance_id = p_instance_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.resource_id = mrr.resource_id
AND mor.alternate_number <> p_alt_num
AND mrr.parent_id =2
and mrr.supply_id = l_supply_id
order by mor.principal_flag;
SELECT mor.resource_usage,
mor.resource_units,
mor.resource_id,
mor.alternate_number,
mor.principal_flag
FROM msc_operation_resources mor
WHERE mor.plan_id = p_plan_id
AND mor.routing_sequence_id = l_rout_seq
AND mor.sr_instance_id = p_instance_id
AND mor.operation_sequence_id = l_op_seq
AND mor.resource_seq_num = l_res_seq
AND mor.alternate_number = p_alt_num
order by mor.principal_flag;
select
mrr.routing_sequence_id, mrr.operation_sequence_id,
mrr.resource_seq_num,mrr.supply_id,mrr.basis_type
into
l_rout_seq, l_op_seq, l_res_seq, l_supply_id,l_basis_type
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id;
select new_order_quantity
into v_qty
from msc_supplies
where plan_id = p_plan_id
and transaction_id = l_supply_id;
select activity_group_id
into l_act_group
from msc_operation_resource_seqs
where plan_id = p_plan_id
and routing_sequence_id = l_rout_seq
and operation_sequence_id = l_op_seq
and resource_seq_num = l_res_seq
and sr_instance_id = p_instance_id;
select count(*)
into l_avail_res_seq
from (
select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors,
msc_operation_resources mor
where mors.plan_id = p_plan_id
and mors.sr_instance_id = p_instance_id
and mors.operation_sequence_id = l_op_seq
and mors.routing_sequence_id = l_rout_seq
and mors.activity_group_id = l_act_group
and mor.plan_id = p_plan_id
and mor.routing_sequence_id = mors.routing_sequence_id
and mor.operation_sequence_id = mors.operation_sequence_id
and mor.sr_instance_id = p_instance_id
and mor.resource_seq_num = mors.resource_seq_num
and mor.alternate_number = p_alt_num
);
insert into msc_resource_requirements(
TRANSACTION_ID ,
PLAN_ID ,
SUPPLY_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
ROUTING_SEQUENCE_ID ,
OPERATION_SEQUENCE_ID ,
RESOURCE_SEQ_NUM ,
RESOURCE_ID ,
DEPARTMENT_ID ,
ALTERNATE_NUM ,
START_DATE ,
END_DATE ,
BKT_START_DATE ,
RESOURCE_HOURS ,
SET_UP ,
BKT_END_DATE ,
TEAR_DOWN ,
AGGREGATE_RESOURCE_ID ,
SCHEDULE_FLAG ,
PARENT_ID ,
STD_OP_CODE ,
WIP_ENTITY_ID ,
ASSIGNED_UNITS ,
BASIS_TYPE ,
OPERATION_SEQ_NUM ,
LOAD_RATE ,
DAILY_RESOURCE_HOURS ,
STATUS ,
APPLIED ,
UPDATED ,
SUBST_RES_FLAG ,
REFRESH_NUMBER ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
SOURCE_ITEM_ID ,
ASSEMBLY_ITEM_ID ,
SUPPLY_TYPE ,
FIRM_START_DATE ,
FIRM_END_DATE ,
FIRM_FLAG )
select msc_resource_requirements_s.nextval,
PLAN_ID ,
SUPPLY_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
ROUTING_SEQUENCE_ID ,
OPERATION_SEQUENCE_ID ,
RESOURCE_SEQ_NUM ,
l_alt_res(i).resource_id ,
DEPARTMENT_ID ,
l_alt_res(i).alternate_number ,
START_DATE ,
END_DATE ,
BKT_START_DATE ,
v_hours ,
SET_UP ,
BKT_END_DATE ,
TEAR_DOWN ,
AGGREGATE_RESOURCE_ID ,
SCHEDULE_FLAG ,
PARENT_ID ,
STD_OP_CODE ,
WIP_ENTITY_ID ,
ASSIGNED_UNITS ,
BASIS_TYPE ,
OPERATION_SEQ_NUM ,
LOAD_RATE ,
DAILY_RESOURCE_HOURS ,
0 ,
2 ,
UPDATED ,
SUBST_RES_FLAG ,
REFRESH_NUMBER ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
SOURCE_ITEM_ID ,
ASSEMBLY_ITEM_ID ,
SUPPLY_TYPE ,
FIRM_START_DATE ,
FIRM_END_DATE ,
FIRM_RESOURCE
from msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = l_simu_res(1).transaction_id
and mrr.sr_instance_id = p_instance_id;
ELSIF i > l_alt_res.LAST THEN -- delete the extra res
select mrr.firm_flag
into l_firm_flag
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = l_simu_res(i).transaction_id
and mrr.sr_instance_id = p_instance_id
for update of mrr.firm_flag nowait;
delete msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = l_simu_res(i).transaction_id
and mrr.sr_instance_id = p_instance_id;
ELSE -- update the res to alt_res
select mrr.firm_flag
into l_firm_flag
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = l_simu_res(i).transaction_id
and mrr.sr_instance_id = p_instance_id
for update of mrr.firm_flag nowait;
update msc_resource_requirements
set status =0,
applied=2,
resource_id = l_alt_res(i).resource_id,
alternate_num = l_alt_res(i).alternate_number,
firm_flag = l_firm_flag,
resource_hours = v_hours
where plan_id = p_plan_id
and transaction_id = l_simu_res(i).transaction_id
and sr_instance_id = p_instance_id;
update msc_supplies
set status = 0, applied = 2
where plan_id = p_plan_id
and transaction_id = l_supply_id;
SELECT mrr2.transaction_id, mrr2.sr_instance_id
FROM msc_resource_requirements mrr1,
msc_resource_requirements mrr2
WHERE mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.operation_seq_num = mrr1.operation_seq_num
and mrr2.resource_seq_num = mrr1.resource_seq_num
and mrr2.alternate_num = mrr1.alternate_num
and mrr2.transaction_id <> mrr1.transaction_id
and mrr2.parent_id = 2;
select mrr.firm_flag,mrr.firm_start_date, mrr.firm_end_date,
mrr.resource_id, mrr.alternate_num, mrr.supply_id,
mrr.start_date, mrr.end_date
into l_firm_flag, l_firm_start, l_firm_end,
l_res_id, l_alt_num, l_supply_id,
l_start, l_end
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
for update of mrr.firm_flag nowait;
update msc_resource_requirements
set status =0,
applied=2,
firm_flag = p_firm_type,
firm_start_date = l_firm_start,
firm_end_date = l_firm_end,
start_date = l_start,
end_date = l_end
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_instance_id;
update msc_supplies
set status =0,
applied=2
where plan_id = p_plan_id
and transaction_id = l_supply_id;
select mrr.resource_id, mrr.alternate_num
into l_res_id, l_alt_num
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = v_transaction_id
and mrr.sr_instance_id = v_instance_id
for update of mrr.firm_flag nowait;
update msc_resource_requirements
set status =0,
applied=2,
firm_flag = p_firm_type,
firm_start_date = l_firm_start,
firm_end_date = l_firm_end,
start_date = l_start,
end_date = l_end
where plan_id = p_plan_id
and transaction_id = v_transaction_id
and sr_instance_id = v_instance_id;
SELECT mor.alternate_number ||':'||
msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id,
mrr.sr_instance_id),
mor.resource_id,
mor.alternate_number
FROM msc_operation_resources mor,
msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
AND mrr.transaction_id = p_transaction_id
AND mrr.sr_instance_id = p_instance_id
AND mor.plan_id = mrr.plan_id
AND mor.routing_sequence_id = mrr.routing_sequence_id
AND mor.sr_instance_id = mrr.sr_instance_id
AND mor.operation_sequence_id = mrr.operation_sequence_id
AND mor.resource_seq_num = mrr.resource_seq_num
AND mor.alternate_number <> mrr.alternate_num;
SELECT nvl(mrr.firm_flag,NO_FIRM)
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
AND mrr.transaction_id = p_transaction_id
AND mrr.sr_instance_id = p_instance_id;
select mors.activity_group_id,
mrr.routing_sequence_id, mrr.operation_sequence_id,
mrr.resource_seq_num
from msc_operation_resource_seqs mors,
msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and mors.plan_id = mrr.plan_id
and mors.routing_sequence_id = mrr.routing_sequence_id
and mors.operation_sequence_id = mrr.operation_sequence_id
and mors.resource_seq_num = mrr.resource_seq_num
and mors.sr_instance_id = mrr.sr_instance_id;
select count(*)
into l_total_seqs
from (Select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors
where mors.plan_id = p_plan_id
and mors.routing_sequence_id = l_rout_seq
and mors.operation_sequence_id = l_op_seq
and mors.sr_instance_id = p_instance_id
and mors.activity_group_id = l_act_group
) ;
select count(*)
into l_avail_seqs
from (
select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors,
msc_operation_resources mor
where mors.plan_id = p_plan_id
and mors.sr_instance_id = p_instance_id
and mors.operation_sequence_id = l_op_seq
and mors.routing_sequence_id = l_rout_seq
and mors.activity_group_id = l_act_group
and mor.plan_id = p_plan_id
and mor.routing_sequence_id = mors.routing_sequence_id
and mor.operation_sequence_id = mors.operation_sequence_id
and mor.sr_instance_id = p_instance_id
and mor.resource_seq_num = mors.resource_seq_num
and mor.alternate_number = alt_number
);
SELECT msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id,
mrr.sr_instance_id),
mor.resource_id
FROM msc_operation_resources mor,
msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
AND mrr.transaction_id = p_transaction_id
AND mrr.sr_instance_id = p_instance_id
AND mor.plan_id = mrr.plan_id
AND mor.routing_sequence_id = mrr.routing_sequence_id
AND mor.sr_instance_id = mrr.sr_instance_id
AND mor.operation_sequence_id = mrr.operation_sequence_id
AND mor.resource_seq_num = mrr.resource_seq_num
AND mor.alternate_number = mrr.alternate_num
AND mor.resource_id <> mrr.resource_id;
SELECT msc_get_name.item_name(ms.inventory_item_id,null,null,null) item,
mrr.operation_seq_num,
ms.new_order_quantity qty,
nvl(to_char(ms.firm_date,format_mask),' ') firm_date,
to_char(ms.new_schedule_date,format_mask) sugg_due_date,
nvl(to_char(ms.need_by_date,format_mask),' ') needby,
nvl(ms.unit_number,'null') unit_number,
nvl(msc_get_name.project(ms.project_id,
ms.organization_id,
ms.plan_id,
ms.sr_instance_id), 'null') project,
nvl(msc_get_name.task( ms.task_id,
ms.project_id,
ms.organization_id,
ms.plan_id,
ms.sr_instance_id),'null') task,
ms.transaction_id,
ms.organization_id,
msc_get_name.org_code(mdr.organization_id, mdr.sr_instance_id) org,
mdr.department_code,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id) job_name,
mrr.assigned_units,
nvl(msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',
nvl(mrr.firm_flag,NO_FIRM)),
msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',0))
firm_flag,
ms.firm_planned_type,
nvl(mrr.alternate_num,0) alternate_num,
mrr.resource_seq_num,
nvl(mdr.resource_code, 'null') resource_code,
mrr.resource_hours,
nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,pe.bill_sequence_id),
'null')
alternate_bom_designator,
nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,pe.bill_sequence_id),
'null')
alternate_routing_designator,
nvl(to_char(msi.planning_time_fence_date, format_mask),' ') time_fence,
0 mtq_time, --get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
nvl(mdr.batchable_flag, 2) batchable,
nvl(mrr.batch_number, -1) batch_number,
nvl(mdr.unit_of_measure,'-1') uom,
nvl(decode(mrr.basis_type, null, '-1',
msc_get_name.lookup_meaning(
'MSC_RES_BASIS_TYPE',mrr.basis_type)),'-1') basis_type,
nvl(decode(mrr.schedule_flag, null, '-1',
msc_get_name.lookup_meaning(
'BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),'-1') schedule_flag,
nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null') EPSD,
nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
nvl(to_char(mrr.UEPSD,format_mask),'null') UEPSD,
nvl(to_char(mrr.UEPCD,format_mask),'null') UEPCD,
nvl(to_char(mrr.ULPSD,format_mask),'null') ULPSD,
nvl(to_char(mrr.ULPCD,format_mask),'null') ULPCD
FROM msc_supplies ms,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_system_items msi,
msc_process_effectivity pe
WHERE pe.plan_id(+) = ms.plan_id
AND pe.sr_instance_id(+) = ms.sr_instance_id
AND pe.process_sequence_id(+) = ms.process_seq_id
AND mrr.plan_id = p_plan_id
AND mrr.transaction_id = p_transaction_id
AND mrr.sr_instance_id = p_instance_id
AND ms.plan_id = mrr.plan_id
AND ms.transaction_id = mrr.supply_id
AND ms.sr_instance_id = mrr.sr_instance_id
AND mdr.plan_id = mrr.plan_id
AND mdr.organization_id = mrr.organization_id
AND mdr.sr_instance_id = mrr.sr_instance_id
AND mdr.department_id = mrr.department_id
AND mdr.resource_id = mrr.resource_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
SELECT mfp2.demand_id, mfp2.sr_instance_id,
sum(nvl(mfp1.allocated_quantity,0)),
mfp2.demand_quantity,
mfp2.demand_date,
mfp2.inventory_item_id
FROM msc_full_pegging mfp1,
msc_full_pegging mfp2
WHERE mfp1.plan_id = p_plan_id
AND mfp1.organization_id = v_org_id
AND mfp1.sr_instance_id = p_instance_id
AND mfp1.transaction_id = v_transaction_id
AND mfp2.plan_id = mfp1.plan_id
AND mfp2.sr_instance_id = mfp1.sr_instance_id
AND mfp2.pegging_id = mfp1.end_pegging_id
group by mfp2.demand_id, mfp2.sr_instance_id,
mfp2.demand_quantity, mfp2.demand_date,
mfp2.inventory_item_id;
SELECT nvl(v_demand_quantity,0) qty,
nvl(to_char(v_demand_date,
format_mask), 'null') demand_date,
msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',
v_demand_id) type,
item_name item
FROM msc_items
WHERE inventory_item_id = v_item_id;
SELECT md.using_requirement_quantity qty,
to_char(md.using_assembly_demand_date,
format_mask) demand_date,
nvl(decode(md.schedule_designator_id, null, md.order_number,
msc_get_name.designator(md.schedule_designator_id)),
'null') name,
msc_get_name.lookup_meaning('MRP_DEMAND_ORIGINATION',
md.origination_type) type,
msc_get_name.item_name(md.inventory_item_id, null,null,null) item,
nvl(md.demand_priority,0) priority,
nvl(msc_get_name.customer(md.customer_id),
'null') customer,
nvl(msc_get_name.customer_site(md.customer_site_id),
'null') customer_site,
nvl(to_char(md.dmd_satisfied_date,format_mask),
'null') satisfied_date,
decode(sign(md.dmd_satisfied_date - md.using_assembly_demand_date),
1, GREATEST(round(md.dmd_satisfied_date -
md.using_assembly_demand_date,2), 0.01), 0) days_late,
nvl(to_char(md.quantity_by_due_date),'null') qty_by_due_date,
msc_get_name.org_code(md.organization_id, md.sr_instance_id) org,
nvl(md.demand_class,'null') demand_class
FROM msc_demands md
WHERE md.plan_id = p_plan_id
AND md.demand_id = v_demand_id
AND md.sr_instance_id =v_instance_id
;
select to_char(
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)),
format_mask) start_date,
to_char(least(g_cutoff_date,
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date))),
format_mask) end_date,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
to_char(mrr.supply_id)) job_name,
msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
mrr.assigned_units,
mrr.transaction_id,
mrr.sr_instance_id
from msc_resource_requirements mrr
where mrr.sr_instance_id = p_instance_id
and mrr.plan_id = p_plan_id
and mrr.organization_id = p_org_id
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
and mrr.department_id = p_dept_id
and mrr.resource_id = p_res_id
and to_date(p_time, format_mask)
BETWEEN decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)) AND
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date))
order by start_date;
select to_char(
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)),
format_mask) start_date,
to_char(least(g_cutoff_date,
decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.end_date,
FIRM_RESOURCE, mrr.end_date,
FIRM_START,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
FIRM_START_RES,
mrr.firm_start_date + (mrr.end_date - mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date))),
format_mask) end_date,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
to_char(mrr.supply_id)) job_name,
msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
mrr.assigned_units,
mrr.transaction_id,
mrr.sr_instance_id
from msc_resource_requirements mrr
where mrr.sr_instance_id = p_instance_id
and mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.end_date is not null
and mrr.department_id <> -1
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
order by start_date;
select distinct
mtp.organization_code||':'||
mdr.department_code||':'||
mdr.resource_code,
mdr.organization_id,
mdr.sr_instance_id,
mdr.department_id,
mdr.resource_id
bulk collect into
v_dept_code,
v_org, v_instance, v_dept, v_res
FROM msc_department_resources mdr,
msc_trading_partners mtp,
msc_form_query mfq
WHERE mdr.plan_id = p_plan_id
AND mdr.organization_id = mfq.number2
AND mdr.sr_instance_id = mfq.number1
AND mdr.department_id = mfq.number3
AND mdr.resource_id = mfq.number4
AND mfq.query_id = g_res_query_id
AND mtp.partner_type = 3
AND mdr.organization_id = mtp.sr_tp_id
AND mdr.sr_instance_id = mtp.sr_instance_id
AND mdr.aggregate_resource_flag =2
ORDER BY 1,2,3 ;
select mro.MINIMUM_TRANSFER_QUANTITY,
mrr.cummulative_quantity, ms.new_order_quantity
into l_mtq,l_cumm_quan, l_order_quan
from msc_routing_operations mro,
msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.transaction_id = p_transaction_id
and mro.routing_sequence_id = mrr.routing_sequence_id
and mro.operation_sequence_id = mrr.operation_sequence_id
and mro.plan_id = p_plan_id
and mro.sr_instance_id = p_instance_id
and ms.plan_id = p_plan_id
and ms.sr_instance_id = p_instance_id
and ms.transaction_id = mrr.supply_id;
SELECT mrr.operation_seq_num,
nvl(mrr.schedule_flag, 0) schedule_flag,
decode(ms.order_type, 27,
1, ms.firm_planned_type) firm_planned_type,
sysdate theDate,
get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time
FROM msc_resource_requirements mrr,
msc_supplies ms
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id;
SELECT mrr2.operation_seq_num, mrr2.resource_seq_num, mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrr2.start_date,
FIRM_RESOURCE, mrr2.start_date,
FIRM_END,
mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
FIRM_END_RES,
mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_START,
mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
FIRM_START_RES,
mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
get_MTQ_time(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
from msc_resource_requirements mrr1,
msc_resource_requirements mrr2,
msc_routings mr
where mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num < mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
select mon.from_op_seq_id from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.to_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num < mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or
mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num desc, mrr2.resource_seq_num desc;
SELECT mrr2.operation_seq_num, mrr2.resource_seq_num,mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrr2.start_date,
FIRM_RESOURCE, mrr2.start_date,
FIRM_END,
mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
FIRM_END_RES,
mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_START,
mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
FIRM_START_RES,
mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
- mrr2.start_date),
nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
from msc_resource_requirements mrr1,
msc_resource_requirements mrr2,
msc_routings mr
where mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num > mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
select mon.to_op_seq_id from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.from_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num > mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or
mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num, mrr2.resource_seq_num;
select decode(nvl(mrr.firm_flag,0),
NO_FIRM, mrr.start_date,
FIRM_RESOURCE, mrr.start_date,
FIRM_END,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
FIRM_END_RES,
mrr.firm_end_date - (mrr.end_date - mrr.start_date),
nvl(mrr.firm_start_date, mrr.start_date)),
msi.PLANNING_TIME_FENCE_DATE
into l_prev_start_date, l_timefence_date
from msc_system_items msi,
msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
AND ms.plan_id = mrr.plan_id
AND ms.transaction_id = mrr.supply_id
AND ms.sr_instance_id = mrr.sr_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
SELECT mrr2.transaction_id, mrr2.sr_instance_id
FROM msc_resource_requirements mrr1,
msc_resource_requirements mrr2
WHERE mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.operation_seq_num = mrr1.operation_seq_num
and mrr2.resource_seq_num = mrr1.resource_seq_num
and mrr2.alternate_num = mrr1.alternate_num
and mrr2.transaction_id <> mrr1.transaction_id
and mrr2.parent_id = 2;
select nvl(mdr.batchable_flag, 2)
into v_flag
from msc_resource_requirements mrr,
msc_department_resources mdr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
AND mdr.plan_id = mrr.plan_id
AND mdr.organization_id = mrr.organization_id
AND mdr.sr_instance_id = mrr.sr_instance_id
AND mdr.department_id = mrr.department_id
AND mdr.resource_id = mrr.resource_id
;
select mrr.firm_flag,mrr.firm_start_date, mrr.firm_end_date,
mrr.resource_id, mrr.alternate_num,
mrr.start_date, mrr.end_date, mrr.supply_id
into l_firm_flag, l_firm_start, l_firm_end,
l_res_id, l_alt_num,
l_start, l_end, l_supply_id
FROM msc_resource_requirements mrr
WHERE mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
for update of mrr.firm_flag nowait;
update msc_resource_requirements
set status =0,
applied=2,
firm_flag = l_firm_flag,
firm_start_date =l_firm_start,
firm_end_date =l_firm_end
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_instance_id;
update msc_supplies
set status =0,
applied=2
where plan_id = p_plan_id
and transaction_id = l_supply_id;
select decode(nvl( firm_flag,0),
NO_FIRM, start_date,
FIRM_RESOURCE, start_date,
FIRM_END,
firm_end_date - ( end_date - start_date),
FIRM_END_RES,
firm_end_date - ( end_date - start_date),
nvl(firm_start_date, start_date))
from msc_resource_requirements
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_instance_id;
select decode(nvl( firm_flag,0),
NO_FIRM, end_date,
FIRM_RESOURCE, end_date,
FIRM_START,
firm_start_date + ( end_date - start_date),
FIRM_START_RES,
firm_start_date + ( end_date - start_date),
nvl(firm_end_date, end_date))
from msc_resource_requirements
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_instance_id;
select distinct number1, number2, number3, number4
from msc_form_query
where query_id =g_find_query_id;
select distinct number1, number5
from msc_form_query
where query_id =g_find_query_id;
where_stat := ' SELECT sr_instance_id, ' ||
' organization_id, '||
' department_id, '||
' resource_id, '||
' transaction_id, ' ||
' r_transaction_id ' ||
' FROM (select mrr.sr_instance_id, '||
' mrr.organization_id, ' ||
' mtp.partner_id, ' ||
' mrr.department_id, '||
' mrr.resource_id, '||
' mrr.transaction_id r_transaction_id, ' ||
' mrr.supply_id transaction_id, ' ||
' ms.inventory_item_id, ' ||
' decode(sign(ms.new_schedule_date '||
'- (ms.need_by_date+1)),1,1,2) late_order, '||
' msc_get_gantt_data.get_start_date( ' ||
'mrr.plan_id, mrr.transaction_id, ' ||
' mrr.sr_instance_id) start_date, '||
' msc_get_gantt_data.get_end_date( ' ||
'mrr.plan_id, mrr.transaction_id, ' ||
' mrr.sr_instance_id) end_date '||
' FROM msc_resource_requirements mrr, ' ||
' msc_supplies ms, ' ||
' msc_trading_partners mtp ' ||
' WHERE ms.plan_id = :1 '||
' and mrr.plan_id = ms.plan_id ' ||
' and mrr.supply_id = ms.transaction_id ' ||
' and mrr.sr_instance_id = ms.sr_instance_id ' ||
' and mrr.organization_id = ms.organization_id ' ||
' and mrr.organization_id = mtp.sr_tp_id ' ||
' and mrr.sr_instance_id = mtp.sr_instance_id ' ||
' and mrr.end_date is not null '||
' and mtp.partner_type = 3 ' ||
' and nvl(mrr.parent_id,2) =2) ';
exc_where_stat := ' SELECT sr_instance_id, ' ||
' organization_id, '||
' department_id, '||
' resource_id, '||
' transaction_id, ' ||
' r_transaction_id ' ||
' FROM (select mrr.sr_instance_id, '||
' mrr.organization_id, ' ||
' mtp.partner_id, ' ||
' med.exception_type, ' ||
' mrr.department_id, '||
' mrr.resource_id, '||
' mrr.transaction_id r_transaction_id, ' ||
' mrr.supply_id transaction_id, ' ||
' ms.inventory_item_id, ' ||
' msc_get_gantt_data.get_start_date( ' ||
'mrr.plan_id, mrr.transaction_id, ' ||
' mrr.sr_instance_id) start_date, '||
' msc_get_gantt_data.get_end_date( ' ||
'mrr.plan_id, mrr.transaction_id, ' ||
' mrr.sr_instance_id) end_date '||
' FROM msc_resource_requirements mrr, ' ||
' msc_supplies ms, ' ||
' msc_trading_partners mtp, ' ||
' msc_exception_details med ' ||
' WHERE ms.plan_id = :1 '||
' and mrr.plan_id = ms.plan_id ' ||
' and mrr.supply_id = ms.transaction_id ' ||
' and mrr.sr_instance_id = ms.sr_instance_id ' ||
' and mrr.organization_id = ms.organization_id ' ||
' and mrr.organization_id = mtp.sr_tp_id ' ||
' and mrr.sr_instance_id = mtp.sr_instance_id ' ||
' and mtp.partner_type = 3 ' ||
' AND med.plan_id = mrr.plan_id ' ||
' AND med.organization_id = mrr.organization_id ' ||
' AND med.sr_instance_id = mrr.sr_instance_id ' ||
' AND decode(med.department_id, -1, mrr.department_id,'||
' med.department_id) = mrr.department_id ' ||
' AND decode(med.resource_id, -1, mrr.resource_id, '||
' med.resource_id) = mrr.resource_id '||
' AND decode(med.inventory_item_id, -1, '||
' mrr.assembly_item_id, '||
' med.inventory_item_id) = mrr.assembly_item_id '||
' and mrr.end_date is not null '||
' and nvl(mrr.parent_id,2) =2 )';
delete msc_form_query
where query_id = g_find_query_id;
select msc_form_query_s.nextval
into g_find_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
NUMBER5,
NUMBER6)
values (
g_find_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
l_instance,
l_org,
l_dept,
l_res,
l_supply,
l_transaction);
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.supply_id ' ||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr ' ||
' WHERE mrr.plan_id = '||p_plan_id ||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' and mrr.end_date is not null '||
' and nvl(mrr.parent_id,2) =2 '||
p_where;
'SELECT mrr.sr_instance_id, '||
' mrr.transaction_id '||
-- ' med.exception_detail_id '||
' FROM msc_supplies mrr, '||
' msc_exception_details med '||
' WHERE mrr.plan_id = '||p_plan_id ||
' and mrr.plan_id = med.plan_id '||
' and ( (mrr.transaction_id = med.number1 and '||
' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
' or (mrr.transaction_id = med.number2 and '||
' med.exception_type = 37)) '|| p_where ||
' union select mrr.sr_instance_id, '||
' mrr.supply_id transaction_id '||
-- ' med.exception_detail_id '||
' FROM msc_resource_requirements mrr, '||
' msc_exception_details med '||
' where med.exception_type in (21,22,36,45,46) '||
' AND med.plan_id = mrr.plan_id ' ||
' AND med.organization_id = mrr.organization_id ' ||
' AND med.sr_instance_id = mrr.sr_instance_id ' ||
' AND med.department_id = mrr.department_id '||
' AND med.plan_id = '||p_plan_id ||
' AND med.resource_id = mrr.resource_id '||
' and mrr.end_date is not null '||
p_where;
sql_stat := 'SELECT mrr.sr_instance_id, '||
' mrr.transaction_id '||
'FROM msc_supplies mrr '||
' WHERE mrr.plan_id = '||p_plan_id ||p_where;
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.organization_id, '||
' mrr.department_id, '||
' mrr.resource_id '||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr, ' ||
' msc_exception_details med ' ||
' WHERE mrr.plan_id = :1 '||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' AND med.plan_id = mrr.plan_id ' ||
' AND med.organization_id = mrr.organization_id ' ||
' AND med.sr_instance_id = mrr.sr_instance_id ' ||
' AND decode(med.department_id, -1, mrr.department_id,'||
' med.department_id) = mrr.department_id ' ||
' AND decode(med.resource_id, -1, mrr.resource_id, '||
' med.resource_id) = mrr.resource_id '||
' AND decode(med.inventory_item_id, -1, '||
' mrr.assembly_item_id, '||
' med.inventory_item_id) = mrr.assembly_item_id '||
' and ( (mrr.supply_id = med.number1 and '||
' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
' or (mrr.supply_id = med.number2 and '||
' med.exception_type = 37) '||
' or (med.exception_type in (21,22,36,45,46)))'||
' and nvl(mrr.parent_id,2) =2 ' ||
' and mrr.end_date is not null '||
' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
p_where;
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.organization_id, '||
' mrr.department_id, '||
' mrr.resource_id '||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr ' ||
' WHERE mrr.plan_id = :1 '||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' and mrr.end_date is not null '||
' and nvl(mrr.parent_id,2) =2 ' ||
' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
p_where;
select number2, number1
from msc_form_query
where query_id = g_supply_query_id;
select distinct number1, number2,number3, number4
from msc_form_query
where query_id = g_res_query_id;
select curr_cutoff_date +1, curr_cutoff_date +2
from msc_plans
where plan_id = p_plan_id;
select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and mpb.bucket_type =1;
select max(mpb.bkt_end_date)
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and mpb.bucket_type =p_bkt_type
;
SELECT greatest(mpsd.period_start_date, mp.data_start_date)
BULK COLLECT INTO v_date
FROM msc_trading_partners tp,
msc_period_start_dates mpsd,
msc_plans mp
WHERE mpsd.calendar_code = tp.calendar_code
and mpsd.sr_instance_id = tp.sr_instance_id
and mpsd.exception_set_id = tp.calendar_exception_set_id
and tp.sr_instance_id = mp.sr_instance_id
and tp.sr_tp_id = mp.organization_id
and tp.partner_type =3
and mp.plan_id = p_plan_id
and (mpsd.period_start_date between mp.data_start_date
and mp.curr_cutoff_date
or mpsd.next_date between mp.data_start_date and
mp.curr_cutoff_date)
order by mpsd.period_start_date;
select nvl(MIN_CUTOFF_BUCKET,0),
nvl(HOUR_CUTOFF_BUCKET,0),
DAILY_CUTOFF_BUCKET
into v_min_day, v_hour_day, v_date_day
from msc_plans
where plan_id = p_plan_id;
SELECT nvl(new_order_quantity,0)
INTO l_quan
FROM msc_supplies
WHERE plan_id = p_plan_id
AND transaction_id = p_supply_id
FOR UPDATE OF firm_date NOWAIT;
UPDATE msc_supplies
SET firm_date = to_date(p_end, format_mask), firm_quantity = l_quan,
applied = 2, status = 0, firm_planned_type = 1
WHERE plan_id = p_plan_id
AND transaction_id = p_supply_id;
select distinct to_char(number2),
number2,
OP_NODE,
0,
0,
0
from msc_form_query
where query_id = p_op_seq_query_id
and number1 = p_supply_id
order by number2;
select distinct ms.organization_id,
ms.transaction_id,
ms.sr_instance_id,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id) ||' for '||
msi.item_name ||
' in ' ||mtp.organization_code ||'('||
ms.new_order_quantity||')',
nvl(ms.firm_planned_type,2),
nvl(ms.status, 0),
nvl(ms.applied,0),
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.ULPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.ULPCD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.UEPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.UEPCD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
decode(g_end_demand_id, null, 0,
msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)),
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id),
mtp.organization_code||':'||msi.item_name,
ms.inventory_item_id,
nvl(ms.supplier_id,-1)
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_system_items msi,
msc_trading_partners mtp,
msc_form_query mfq
where mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_supply_id
and mfp1.sr_instance_id = p_instance_id
and mfp1.end_pegging_id = mfq.number1
and mfq.query_id = p_end_peg_query_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.prev_pegging_id = mfp1.pegging_id
and ms.plan_id = mfp2.plan_id
and ms.transaction_id = mfp2.transaction_id
and ms.sr_instance_id = mfp2.sr_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and mtp.partner_type=3
and mtp.sr_tp_id=ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id
and ms.transaction_id not in (
select mfq.number3
from msc_form_query mfq
where mfq.query_id = p_query_id
and mfq.number1 = p_supply_id
and mfq.number2 is not null -- op_seq_num is not null
);
select distinct
mfq.number3, -- ms.transaction_id,
mfq.number4, -- ms.sr_instance_id,
mfq.number5, -- ms.organization_id,
mfq.char10 || ' for '|| -- ms.order_number
mi.item_name ||
' in ' ||mtp.organization_code ||'('||
mfq.number11||')',
mfq.number7, -- nvl(ms.firm_planned_type,2),
mfq.number8, -- nvl(ms.status, 0),
mfq.number9, -- nvl(ms.applied,0),
mfq.number10, -- late flag
mfq.char1,
mfq.char2,
mfq.char3,
mfq.char4,
mfq.char5,
mfq.char6,
mfq.char7,
mfq.char8,
mfq.char9,
mfq.number12,
mfq.number13,
mtp.organization_code||':'||mi.item_name,
mfq.number14,
mfq.number15
from msc_form_query mfq,
msc_items mi,
msc_trading_partners mtp
where mfq.query_id = p_query_id
and mfq.number1 = p_supply_id
and mfq.number2 =p_op_seq_num
and mi.inventory_item_id = mfq.number6
and mtp.partner_type=3
and mtp.sr_tp_id=mfq.number5
and mtp.sr_instance_id = mfq.number4;
select to_char(mrr.operation_seq_num)||'/'
||to_char(mrr.resource_seq_num)||
'('||msc_get_name.department_resource_code(mrr.resource_id,
mrr.department_id, mrr.organization_id,
mrr.plan_id, mrr.sr_instance_id)||')',
to_char(msc_get_gantt_data.get_start_date(
mrr.plan_id, mrr.transaction_id, mrr.sr_instance_id),
format_mask),
to_char(nvl(msc_get_gantt_data.get_end_date(
mrr.plan_id, mrr.transaction_id, mrr.sr_instance_id),
mrr.start_date),
format_mask),
mrr.transaction_id,
nvl(mrr.department_id, 0),
nvl(mrr.resource_id, 0),
nvl(mrr.status, 0),
nvl(mrr.applied, 0),
nvl(mrr.firm_flag, 0),
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.ULPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.ULPCD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.UEPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(mrr.UEPCD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
decode(g_end_demand_id, null, 0,
msc_get_gantt_data.isCriticalRes(p_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id,
mrr.operation_seq_num, mrr.routing_sequence_id))
from msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.supply_id = p_supply_id
and mrr.parent_id =2
and mrr.operation_seq_num = p_op_seq
and mrr.sr_instance_id = p_instance_id
and mrr.organization_id = p_org_id
and mrr.end_date is not null
and mrr.department_id <> -1
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
order by 2,3,1;
select to_char(nvl(decode(nvl(firm_planned_type,2),2,
new_dock_date,
new_dock_date+(firm_date-new_schedule_date)),
new_schedule_date),
format_mask),
to_char(decode(nvl(firm_planned_type,2),2,
new_schedule_date,nvl(firm_date,new_schedule_date)),
format_mask),
nvl(firm_planned_type,2)
from msc_supplies
where plan_id = p_plan_id
and transaction_id = p_supply_id;
select msc_form_query_s.nextval
into p_end_peg_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1)
select
p_end_peg_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
mfp.end_pegging_id
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
and mfp.demand_id = g_end_demand_id;
select msc_form_query_s.nextval
into p_op_seq_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1, -- supply_id
NUMBER2) -- op_seq
select distinct
p_op_seq_query_id,
trunc(sysdate),
-1,
trunc(sysdate),
-1,
-1,
p_supply_id,
mrr.operation_seq_num
from msc_resource_requirements mrr,
msc_critical_paths mcp
where mrr.plan_id = p_plan_id
and mrr.supply_id = p_supply_id
and mrr.sr_instance_id = p_instance_id
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and mrr.department_id <> -1
and mrr.organization_id = p_org_id
and mrr.plan_id = mcp.plan_id
and mrr.sr_instance_id = mcp.sr_instance_id
and mrr.supply_id = mcp.supply_id
and nvl(mrr.routing_sequence_id,-1) =
nvl(mcp.routing_sequence_id,-1)
and mrr.operation_seq_num = mcp.operation_sequence_id
and mcp.demand_id = g_end_demand_id
and nvl(mcp.path_number,1) =
decode(p_critical,0,0,nvl(mcp.path_number,1));
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1, -- supply_id
NUMBER2) -- op_seq
select distinct
p_op_seq_query_id,
trunc(sysdate),
-1,
trunc(sysdate),
-1,
-1,
p_supply_id,
operation_seq_num
from msc_resource_requirements
where plan_id = p_plan_id
and supply_id = p_supply_id
and sr_instance_id = p_instance_id
and end_date is not null
and department_id <> -1
and nvl(parent_id,2) =2
and organization_id = p_org_id;
select msc_form_query_s.nextval
into p_query_id
from dual;
select msc_form_query_s.nextval
into p_end_peg_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1)
select
p_end_peg_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
mfp.end_pegging_id
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
and mfp.transaction_id = p_end_supply_id;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1, -- supply_id
NUMBER2, -- op_seq
NUMBER3, -- tran_id
NUMBER4, -- inst_id
NUMBER5, -- org_id
NUMBER6, -- item_id
NUMBER7, --firm_type
NUMBER8, -- status
NUMBER9, -- applied
NUMBER10, -- late_flag
NUMBER11, -- qty
CHAR10, -- order_number
CHAR1, -- start date
CHAR2, -- end date
CHAR3, -- early start date
CHAR4, -- early end date
CHAR5, -- latest start date
CHAR6, -- latest end date
CHAR7, -- min start
CHAR8, -- u early start date
CHAR9, -- u early end date
NUMBER12, -- critical_flag
NUMBER13, -- supply type
NUMBER14, -- item_id
NUMBER15) -- supplier_id
select distinct
p_query_id,
trunc(sysdate),
-1,
trunc(sysdate),
-1,
-1,
p_supply_id,
decode(md.op_seq_num,1,p_first_op,md.op_seq_num),
ms.transaction_id,
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(ms.firm_planned_type,2),
nvl(ms.status, 0),
nvl(ms.applied,0),
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
ms.new_order_quantity,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null'),
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null'),
nvl(to_char(ms.ULPSD,format_mask),'null'),
nvl(to_char(ms.ULPCD,format_mask),'null'),
nvl(to_char(ms.MIN_START_DATE,format_mask), 'null'),
nvl(to_char(ms.UEPSD,format_mask),'null'),
nvl(to_char(ms.UEPCD,format_mask),'null'),
1,
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id),
ms.inventory_item_id,
nvl(ms.supplier_id,-1)
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_demands md,
msc_system_items msi,
msc_critical_paths mcp,
msc_form_query mfq
where mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_supply_id
and mfp1.end_pegging_id = mfq.number1
and mfq.query_id = p_end_peg_query_id
and md.plan_id = mfp1.plan_id
and md.disposition_id = mfp1.transaction_id
and md.sr_instance_id = mfp1.sr_instance_id
and nvl(md.op_seq_num,0) <> 0
and mfp2.plan_id = mfp1.plan_id
and mfp2.prev_pegging_id = mfp1.pegging_id
and mfp2.demand_id = md.demand_id
and ms.plan_id = mfp2.plan_id
and ms.transaction_id = mfp2.transaction_id
and mcp.plan_id = ms.plan_id
and mcp.supply_id = ms.transaction_id
and mcp.sr_instance_id = ms.sr_instance_id
and mcp.demand_id = g_end_demand_id
-- and mcp.routing_sequence_id is null
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and nvl(mcp.path_number,1) =
decode(p_critical,0,0,nvl(mcp.path_number,1))
;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1, -- supply_id
NUMBER2, -- op_seq
NUMBER3, -- tran_id
NUMBER4, -- inst_id
NUMBER5, -- org_id
NUMBER6, -- item_id
NUMBER7, --firm_type
NUMBER8, -- status
NUMBER9, -- applied
NUMBER10, -- late_flag
NUMBER11, -- qty
CHAR10, -- order_number
CHAR1, -- start date
CHAR2, -- end date
CHAR3, -- early start date
CHAR4, -- early end date
CHAR5, -- latest start date
CHAR6, -- latest end date
CHAR7, -- min start
CHAR8, -- u early start date
CHAR9, -- u early end date
NUMBER12, -- critical_flag
NUMBER13, -- supply type
NUMBER14, -- item_id
NUMBER15) -- supplier_id
select distinct
p_query_id,
trunc(sysdate),
-1,
trunc(sysdate),
-1,
-1,
p_supply_id,
decode(md.op_seq_num,1,p_first_op,md.op_seq_num),
ms.transaction_id,
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(ms.firm_planned_type,2),
nvl(ms.status, 0),
nvl(ms.applied,0),
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
ms.new_order_quantity,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.ULPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.ULPCD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.UEPSD,format_mask),'null')),
decode(g_end_demand_id, null, 'null',
nvl(to_char(ms.UEPCD,format_mask),'null')),
decode(g_end_demand_id, null, 0,
msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)),
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id),
ms.inventory_item_id,
nvl(ms.supplier_id,-1)
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_system_items msi,
msc_supplies ms,
msc_demands md,
msc_form_query mfq
where mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_supply_id
and mfp1.end_pegging_id = mfq.number1
and mfq.query_id = p_end_peg_query_id
and md.plan_id = mfp1.plan_id
and md.disposition_id = mfp1.transaction_id
and md.sr_instance_id = mfp1.sr_instance_id
and nvl(md.op_seq_num,0) <> 0
and mfp2.plan_id = mfp1.plan_id
and mfp2.prev_pegging_id = mfp1.pegging_id
and mfp2.demand_id = md.demand_id
and ms.plan_id = mfp2.plan_id
and ms.transaction_id = mfp2.transaction_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
select distinct mfq.number2, mfq.number2
bulk collect into v_op, v_new_op
from msc_form_query mfq
where mfq.query_id = p_query_id
and mfq.number1 = p_supply_id
and mfq.number2 not in (
select mfq_mrr.number2
from msc_form_query mfq_mrr
where mfq_mrr.query_id = p_op_seq_query_id
and mfq_mrr.number1 = p_supply_id);
select min(number2)
into v_dummy
from msc_form_query
where query_id = p_op_seq_query_id
and number1 = p_supply_id
and number2 > v_op(a);
select max(number2)
into v_dummy
from msc_form_query
where query_id = p_op_seq_query_id
and number1 = p_supply_id
and number2 < v_op(a);
update msc_form_query
set number2= v_new_op(a)
where query_id = p_query_id
and number1 = p_supply_id
and number2 = v_op(a);
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
char1)
values (
g_supplier_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
v_supplier_id,
v_org_id,
v_instance_id,
v_item_id,
v_org_code);
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
char1)
values (
g_supplier_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
v_supplier_id,
peg_data.org_id(i),
peg_data.instance_id(i),
v_item_id,
v_org_code);
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4)
values (
g_res_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
p_instance_id,
p_org_id,
v_dept_id,
v_res_id);
select distinct ms.organization_id,
ms.transaction_id, ms.sr_instance_id,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id) ||' for '||
msi.item_name ||
' in ' || mtp.organization_code,
1, -- mfp1.pegging_id,
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id)
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_form_query mfq,
msc_supplies ms,
msc_system_items msi,
msc_trading_partners mtp
where mfp1.pegging_id = mfp2.end_pegging_id
and mfp1.plan_id = mfp2.plan_id
and mfp1.sr_instance_id = mfp2.sr_instance_id
and mfp2.plan_id = p_plan_id
and mfp2.transaction_id = mfq.number1
and mfp2.sr_instance_id = mfq.number2
and mfq.query_id = g_supply_query_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and mtp.partner_type =3
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id
and ms.plan_id = mfp1.plan_id
and ms.transaction_id = mfp1.transaction_id
and ms.sr_instance_id = mfp1.sr_instance_id
order by ms.transaction_id;
select distinct ms.organization_id,
ms.transaction_id, ms.sr_instance_id,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id)||' for '||
msi.item_name ||
' in ' || mtp.organization_code,
1, -- mfp1.pegging_id,
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id)
from msc_full_pegging mfp2,
msc_form_query mfq,
msc_supplies ms,
msc_system_items msi,
msc_trading_partners mtp
where mfp2.plan_id = p_plan_id
and mfp2.transaction_id = mfq.number1
and mfp2.sr_instance_id = mfq.number2
and mfq.query_id = g_supply_query_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and mtp.partner_type =3
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id
and ms.plan_id = mfp2.plan_id
and ms.transaction_id = mfp2.transaction_id
and ms.sr_instance_id = mfp2.sr_instance_id
order by ms.transaction_id;
delete msc_form_query
where query_id = g_supply_query_id;
select msc_form_query_s.nextval
into g_supply_query_id
from dual;
delete msc_form_query
where query_id = g_res_query_id;
select msc_form_query_s.nextval
into g_res_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2)
values (
g_supply_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
v_transaction_id,
v_instance_id);
SELECT msc_get_name.item_name(ms.inventory_item_id,null,null,null) item,
ms.new_order_quantity qty,
nvl(to_char(ms.firm_date,format_mask), ' ') firm_date,
to_char(ms.new_schedule_date,format_mask) sugg_due_date,
nvl(to_char(ms.need_by_date,format_mask), ' ') needby,
nvl(ms.unit_number,'null') unit_number,
nvl(msc_get_name.project(ms.project_id,
ms.organization_id,
ms.plan_id,
ms.sr_instance_id), 'null') project,
nvl(msc_get_name.task( ms.task_id,
ms.project_id,
ms.organization_id,
ms.plan_id,
ms.sr_instance_id),'null') task,
msc_get_name.org_code(ms.organization_id, ms.sr_instance_id) org,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id) job_name,
ms.firm_planned_type,
nvl(ms.alternate_bom_designator, 'null') alternate_bom_designator,
nvl(ms.alternate_routing_designator, 'null')
alternate_routing_designator,
ms.organization_id org_id,
nvl(to_char(msi.planning_time_fence_date, format_mask),' ') time_fence,
msc_get_name.supply_type(ms.transaction_id, p_plan_id) supply_type,
decode(msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id),
BUY_SUPPLY, g_buy_text,
TRANSFER_SUPPLY, g_transfer_text,
MAKE_SUPPLY, g_make_text) item_type,
msi.description,
nvl(msc_get_name.supplier(
nvl(ms.source_supplier_id, ms.supplier_id)),'-1') supplier,
nvl(msc_get_name.org_code(ms.source_organization_id,
ms.source_sr_instance_id),'-1') source_org,
nvl(ms.ship_method, '-1') ship_method,
msc_get_name.lookup_meaning('SYS_YES_NO',
decode(ms.supply_is_shared,1,1,2)) share_supply,
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null') EPSD,
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
nvl(to_char(ms.UEPSD,format_mask),'null') UEPSD,
nvl(to_char(ms.UEPCD,format_mask),'null') UEPCD,
nvl(to_char(ms.ULPSD,format_mask),'null') ULPSD,
nvl(to_char(ms.ULPCD,format_mask),'null') ULPCD
FROM msc_supplies ms,
msc_system_items msi
WHERE ms.plan_id = p_plan_id
AND ms.transaction_id = p_transaction_id
and ms.sr_instance_id = p_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
SELECT msc_get_name.item_name(mrr.assembly_item_id,null,null,null) item,
nvl(mrr.operation_seq_num,0) op_seq,
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id) org,
msc_get_name.department_code(decode(mrr.resource_id, -1, 1,2),
mrr.department_id, mrr.organization_id,
mrr.plan_id, mrr.sr_instance_id) dept_code,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
to_char(mrr.supply_id)) job_name,
nvl(mrr.assigned_units,0) assigned_units,
msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',
nvl(mrr.firm_flag,0)) firm_flag,
nvl(mrr.alternate_num,0) alternate_num,
nvl(mrr.resource_seq_num,0) res_seq_num,
nvl(msc_get_name.resource_code(mrr.resource_id,
mrr.department_id, mrr.organization_id,
mrr.plan_id, mrr.sr_instance_id)
, 'null') res_code,
nvl(mrr.resource_hours,0) resource_hours,
ms.organization_id org_id,
ms.transaction_id trans_id,
0 mtq_time, -- get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
nvl(mdr.batchable_flag,2) batchable,
nvl(mrr.batch_number, -1) batch_number,
nvl(mdr.unit_of_measure,'-1') uom,
nvl(decode(mrr.basis_type, null, '-1',
msc_get_name.lookup_meaning(
'MSC_RES_BASIS_TYPE',mrr.basis_type)),'-1') basis_type,
nvl(decode(mrr.schedule_flag, null, '-1',
msc_get_name.lookup_meaning(
'BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),'-1') schedule_flag,
nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null') EPSD,
nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
nvl(to_char(mrr.UEPSD,format_mask),'null') UEPSD,
nvl(to_char(mrr.UEPCD,format_mask),'null') UEPCD,
nvl(to_char(mrr.ULPSD,format_mask),'null') ULPSD,
nvl(to_char(mrr.ULPCD,format_mask),'null') ULPCD
FROM msc_resource_requirements mrr,
msc_supplies ms,
msc_department_resources mdr
WHERE mrr.plan_id = p_plan_id
AND mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.plan_id = p_plan_id
and ms.transaction_id = mrr.supply_id
AND mdr.plan_id = mrr.plan_id
AND mdr.organization_id = mrr.organization_id
AND mdr.sr_instance_id = mrr.sr_instance_id
AND mdr.department_id = mrr.department_id
AND mdr.resource_id = mrr.resource_id
;
select sum(decode(mfp.demand_id, -1, mfp.allocated_quantity, 0)),
sum(mfp.allocated_quantity)
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
AND mfp.transaction_id = p_transaction_id
and mfp.end_pegging_id = p_end_peg_id;
select mfp.end_pegging_id
bulk collect into v_end_peg_id
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
and mfp.demand_id = g_end_demand_id
and mfp.pegging_id = mfp.end_pegging_id;
peg_data.parent_index.delete;
peg_data.next_record.delete;
peg_data.org_id.delete;
peg_data.transaction_id.delete;
peg_data.instance_id.delete;
peg_data.department_id.delete;
peg_data.op_seq.delete;
peg_data.type.delete;
peg_data.path.delete;
peg_data.name.delete;
peg_data.firm_flag.delete;
peg_data.start_date.delete;
peg_data.end_date.delete;
peg_data.status.delete;
peg_data.applied.delete;
peg_data.res_firm_flag.delete;
peg_data.late_flag.delete;
peg_data.early_start_date.delete;
peg_data.early_end_date.delete;
peg_data.u_early_start_date.delete;
peg_data.u_early_end_date.delete;
peg_data.latest_start_date.delete;
peg_data.latest_end_date.delete;
peg_data.min_start_date.delete;
peg_data.critical_flag.delete;
peg_data.supply_type.delete;
peg_data.new_path.delete;
select nvl(trunc(mis.SUPPLIER_LEAD_TIME_DATE +1),
trunc(mp.plan_start_date+2))
from msc_item_suppliers mis,
msc_plans mp
where mis.plan_id = mp.plan_id
and mis.inventory_item_id = v_item_id
and mis.sr_instance_id = v_instance_id
and mis.supplier_id = v_supplier_id
and mis.organization_id = v_org_id
and mp.plan_id = p_plan_id;
select mpb.bkt_start_date,mpb.bkt_end_date
BULK COLLECT INTO v_bkt_start, v_bkt_end
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and ( mpb.bkt_start_date between p_start_date and p_end_date
or
mpb.bkt_end_date between p_start_date and p_end_date )
and mpb.bucket_type <> 1
order by 1;
select
mca.calendar_date, mca.calendar_date+1, msc.capacity
bulk collect into v_start, v_end, v_qty
from msc_calendar_dates mca,
msc_plans mp,
msc_trading_partners mtp,
msc_supplier_capacities msc
where msc.plan_id = p_plan_id
and msc.inventory_item_id = v_item_id
and msc.sr_instance_id = v_instance_id
and msc.supplier_id = v_supplier_id
and msc.organization_id = v_org_id
and msc.capacity > 0
and msc.from_date <=p_end_date
and msc.to_date >=v_lead_time_date
and mp.plan_id = msc.plan_id
and mtp.sr_tp_id = mp.organization_id
and mtp.sr_instance_id = mp.sr_instance_id
and mtp.partner_type =3
and mca.sr_instance_id= mtp.sr_instance_id
and mca.calendar_code = mtp.calendar_code
and mca.exception_set_id = mtp.calendar_exception_set_id
and mca.calendar_date between msc.from_date and msc.to_date
and mca.seq_num is not null
order by msc.transaction_id,msc.from_date, msc.to_date;
select trunc(ms.new_dock_date), trunc(ms.new_dock_date)+1,
ms.new_order_quantity
bulk collect into v_start, v_end, v_qty
from msc_supplies ms
where ms.plan_id = p_plan_id
and ms.inventory_item_id = v_item_id
and ms.sr_instance_id = v_instance_id
and ms.supplier_id = v_supplier_id
and ms.organization_id = v_org_id
and ms.new_dock_date <= p_end_date
and (ms.order_type <> 1 -- not for PO
or
(ms.order_type = 1 and
ms.promised_date is null and
p_promise_date_profile = 1)) -- promised_date
order by ms.new_dock_date;
oneBigRecord.delete;
oneBigRecord.delete;
v_req_start.delete;
v_req_end.delete;
v_req_qty.delete;
v_req_qty_unmet.delete;
select md.organization_id,
md.demand_id, md.sr_instance_id,
nvl(md.order_number,
nvl(msc_get_name.designator(md.schedule_designator_id),
md.demand_id)) ||' for '||
mi.item_name ||
' in ' || mtp.organization_code ||'('||
md.using_requirement_quantity||')',
to_char(md.using_assembly_demand_date,format_mask),
to_char(nvl(md.dmd_satisfied_date,md.using_assembly_demand_date),format_mask),
demand_priority
from msc_demands md,
msc_items mi,
msc_trading_partners mtp
where md.demand_id = p_demand_id
and md.plan_id = p_plan_id
and mi.inventory_item_id = md.inventory_item_id
and mtp.partner_type =3
and mtp.sr_tp_id = md.organization_id
and mtp.sr_instance_id = md.sr_instance_id;
select distinct ms.organization_id,
ms.transaction_id, ms.sr_instance_id,
msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
ms.plan_id, ms.sr_instance_id,
ms.transaction_id, ms.disposition_id)||' for '||
msi.item_name ||
' in ' || mtp.organization_code ||'('||
ms.new_order_quantity||')',
msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
ms.organization_id,ms.inventory_item_id,ms.transaction_id),
msc_get_gantt_data.actualStartDate(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id,
ms.new_dock_date,
ms.new_wip_start_date,
ms.new_ship_date,
ms.new_schedule_date),
nvl(to_char(ms.new_schedule_date,format_mask),'null'),
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null'),
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null'),
nvl(to_char(ms.ULPSD,format_mask),'null'),
nvl(to_char(ms.ULPCD,format_mask),'null'),
nvl(to_char(ms.UEPSD,format_mask),'null'),
nvl(to_char(ms.UEPCD,format_mask),'null'),
nvl(to_char(ms.MIN_START_DATE,format_mask), 'null'),
msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id),
msc_get_gantt_data.supplyType(ms.order_type,
msi.planning_make_buy_code,
ms.organization_id,
ms.source_organization_id),
mtp.organization_code ||':'||msi.item_name,
ms.inventory_item_id,
nvl(ms.supplier_id,-1)
from msc_full_pegging mfp,
msc_supplies ms,
msc_system_items msi,
msc_trading_partners mtp
where mfp.demand_id = p_demand_id
and mfp.plan_id = p_plan_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and mtp.partner_type =3
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id
and ms.plan_id = mfp.plan_id
and ms.transaction_id = mfp.transaction_id
and ms.sr_instance_id = mfp.sr_instance_id
order by ms.transaction_id;
delete msc_form_query
where query_id = g_supply_query_id;
select msc_form_query_s.nextval
into g_supply_query_id
from dual;
delete msc_form_query
where query_id = g_res_query_id;
select msc_form_query_s.nextval
into g_res_query_id
from dual;
delete msc_form_query
where query_id = g_supplier_query_id;
select msc_form_query_s.nextval
into g_supplier_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2)
values (
g_supply_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
curr_trans_id(a),
curr_inst_id(a));
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
char1)
values (
g_supplier_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
curr_supplier_id(a),
curr_org_id(a),
curr_inst_id(a),
curr_item_id(a),
curr_org_code(a));
select distinct
mfq.char1,
mtp.partner_name,
mfq.number2,
mfq.number3,
mfq.number4,
mfq.number1
bulk collect into
v_org_code, v_supplier_name,
v_org, v_instance, v_item, v_supplier
FROM msc_trading_partners mtp,
msc_form_query mfq
where mfq.query_id = g_supplier_query_id
AND mtp.partner_type = 1
AND mtp.partner_id = mfq.number1
ORDER BY 1,2 ;
select distinct
md.demand_id,
nvl(md.order_number,
msc_get_name.designator(md.schedule_designator_id))
||'('||md.using_assembly_demand_date||','||
md.USING_REQUIREMENT_QUANTITY||')' ,
md.using_assembly_demand_date
bulk collect into v_demand_id, v_order_name , v_dummy_date
FROM
msc_exception_details med,
msc_demands md,
msc_demands md2
where md2.plan_id = p_plan_id
and md2.demand_id = p_demand_id
and med.plan_id = md2.plan_id
and med.organization_id = md2.organization_id
and med.sr_instance_id = md2.sr_instance_id
and med.inventory_item_id = md2.inventory_item_id
and med.exception_type in (24,26)
and md.plan_id = med.plan_id
and md.demand_id = med.number1
order by md.using_assembly_demand_date ;
select nvl(path_number,1)
from msc_critical_paths
where plan_id = p_plan_id
and supply_id = p_transaction_id
and sr_instance_id = p_inst_id
and demand_id = p_end_demand_id
-- and routing_sequence_id is null
;
select nvl(path_number,1)
from msc_critical_paths
where plan_id = p_plan_id
and supply_id = p_transaction_id
and sr_instance_id = p_inst_id
and demand_id = p_end_demand_id
and nvl(routing_sequence_id,-1) = nvl(p_routing_seq_id,-1)
and operation_sequence_id = p_operation_seq_id;
select decode(ms.firm_planned_type, 1, -1,
msc_get_gantt_data.get_dmd_priority(
ms.plan_id, ms.sr_instance_id, ms.transaction_id)),
ms.new_order_quantity
bulk collect into v_id,v_qty
from msc_supplies ms
where ms.plan_id = p_plan_id
and ms.inventory_item_id = p_item_id
and ms.sr_instance_id = p_instance_id
and ms.supplier_id = p_supplier_id
and ms.organization_id = p_org_id
and trunc(ms.new_dock_date) >= trunc(p_start_date)
and trunc(ms.new_dock_date) < trunc(p_end_date)
and (ms.order_type <> 1 -- not for PO
or
(ms.order_type = 1 and
ms.promised_date is null and
p_promise_date_profile = 1)); -- promised_date
select decode(ms.firm_planned_type, 1, -1,
msc_get_gantt_data.get_dmd_priority(
mrr.plan_id, mrr.sr_instance_id, mrr.supply_id)),
mrr.resource_hours/24/v_bkt_size
bulk collect into v_id,v_qty
from msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.department_id = p_dept_id
and mrr.sr_instance_id = p_instance_id
and mrr.resource_id = p_resource_id
and mrr.organization_id = p_org_id
and mrr.parent_id =1
and mrr.resource_hours > 0
and mrr.end_date is not null
and mrr.start_date <= g_cutoff_date
and mrr.start_date < trunc(p_end_date)
and mrr.start_date >= trunc(p_start_date)
and mrr.plan_id = ms.plan_id
and mrr.supply_id = ms.transaction_id
and mrr.sr_instance_id = ms.sr_instance_id;
SELECT min(md.demand_priority)
FROM msc_demands md,
msc_full_pegging mfp2,
msc_full_pegging mfp1
WHERE mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_transaction_id
and mfp1.sr_instance_id = p_instance_id
and mfp2.pegging_id = mfp1.end_pegging_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.sr_instance_id = mfp1.sr_instance_id
and md.plan_id = mfp2.plan_id
and md.sr_instance_id = mfp2.sr_instance_id
and md.demand_id = mfp2.demand_id
and md.demand_priority is not null;
g_block_start_row.delete;
select 1
from msc_exception_details
WHERE number1 = p_transaction_id
AND sr_instance_id = p_instance_id
AND plan_id = p_plan_id
and exception_type =36
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
select order_number
from msc_supplies
where plan_id = p_plan_id
and transaction_id = p_disposition_id
and sr_instance_id = p_inst_id;