[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select md.sr_instance_id,
md.organization_id,
md.demand_id
from msc_demands md
where md.demand_id = x_demand_id
and md.plan_id = x_plan_id
and md.origination_type in (6,7,8,9,11,15,22,29,30);
select md.sr_instance_id,
md.organization_id,
md.demand_id,
md.using_assembly_demand_date start_date
from msc_demands md,
msc_full_pegging mfp1
where mfp1.plan_id = x_plan_id
and mfp1.transaction_id = x_supply_id
and mfp1.sr_instance_id = x_instance_id
and mfp1.plan_id = md.plan_id
and mfp1.demand_id = md.demand_id
and mfp1.sr_instance_id = md.sr_instance_id
and md.origination_type in (6,7,8,9,11,15,22,29,30)
order by md.using_assembly_demand_date;
select distinct ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
msc_gantt_utils.isCriticalSupply(x_plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id) critical_supply
from msc_full_pegging mfp,
msc_supplies ms
where mfp.demand_id = x_demand_id
and mfp.plan_id = x_plan_id
and ms.plan_id = mfp.plan_id
and ms.transaction_id = mfp.transaction_id
and ms.sr_instance_id = mfp.sr_instance_id;
select distinct
to_char(number2),
number2,
number3,
char9,
date1
from msc_form_query
where query_id = x_op_seq_query_id
and number1 = x_supply_id
/*
and number2 not in
(select mon.to_op_seq_num
from msc_operation_networks mon,
msc_resource_requirements mrr
where mrr.plan_id = x_plan_id
and mrr.sr_instance_id = x_inst_id
and mrr.supply_id = x_supply_id
and mrr.end_date is not null
and mrr.department_id <> -1
and nvl(mrr.parent_id,2) = 2
and mrr.plan_id = mon.plan_id
and mrr.sr_instance_id = mon.sr_instance_id
and mrr.routing_sequence_id = mon.routing_sequence_id
and mrr.operation_seq_num = mon.from_op_seq_num
and mon.transition_type = 1 --primary
and mon.to_op_seq_num is not null
)
order by number2;
select
mon.from_op_seq_num,
mgq1.row_index from_index,
mon.to_op_seq_num,
mgq2.row_index to_index,
mon.dependency_type
from msc_operation_networks mon,
msc_resource_requirements mrr,
msc_gantt_query mgq1,
msc_gantt_query mgq2
where mrr.plan_id = x_plan_id
and mrr.sr_instance_id = x_inst_id
and mrr.supply_id = x_supply_id
and mrr.end_date is not null
and mrr.department_id <> -1
and nvl(mrr.parent_id,2) = 2
and mrr.plan_id = mon.plan_id
and mrr.sr_instance_id = mon.sr_instance_id
--and mrr.organization_id = mon.organization_id --org in mon is null
and mrr.routing_sequence_id = mon.routing_sequence_id
and mon.transition_type = 1 --primary
and mon.from_op_seq_num = mrr.operation_seq_num
and mgq1.query_id = g_order_query_id
and mgq1.sr_instance_id = mon.sr_instance_id
and mgq1.transaction_id = mrr.supply_id
and mgq1.op_seq_num = mon.from_op_seq_num
and mgq2.query_id = g_order_query_id
and mgq2.sr_instance_id = mon.sr_instance_id
and mgq2.transaction_id = mrr.supply_id
and mgq2.op_seq_num = mon.to_op_seq_num
order by
mon.from_op_seq_num,
mon.to_op_seq_num;
select
nvl(mrr.department_id, 0) department_id,
nvl(mrr.resource_id, 0) resource_id,
mrr.transaction_id,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalRes(x_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id,
mrr.operation_seq_num, mrr.routing_sequence_id)) critical_flag,
mrr.operation_seq_num,
mrr.resource_seq_num
from msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = x_plan_id
and mrr.sr_instance_id = x_instance_id
and mrr.organization_id = x_org_id
and mrr.supply_id = x_supply_id
and mrr.operation_seq_num = x_op_seq
and mrr.end_date is not null
and mrr.parent_id =2
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
mrr.operation_seq_num,
mrr.resource_seq_num;
select distinct
mfq.number4,
mfq.number5,
mfq.number3,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)) critical_supply,
msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) start_date,
ms.order_type
from msc_form_query mfq,
msc_supplies ms,
msc_system_items msi
where mfq.query_id = x_dem_op_query_id
and mfq.number1 = x_supply_id
and ms.plan_id = x_plan_id
and ms.sr_instance_id = x_inst_id
and ms.transaction_id = mfq.number3
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
order by msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) desc;
select distinct ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)) critical_supply,
msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) start_date,
ms.order_type
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_form_query mfq,
msc_supplies ms,
msc_system_items msi
where mfp1.plan_id = x_plan_id
and mfp1.sr_instance_id = x_instance_id
and mfp1.transaction_id = x_supply_id
and mfq.query_id = x_end_peg_query_id
and mfq.number1 = mfp1.end_pegging_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.sr_instance_id = mfp2.sr_instance_id
and ms.transaction_id = mfp2.transaction_id
and ms.transaction_id not in (
select mfq.number3
from msc_form_query mfq
where mfq.query_id = x_dem_op_query_id
and mfq.number1 = x_supply_id
and mfq.number2 is not null
)
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
order by msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) desc;
select distinct ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)) critical_supply,
msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) start_date,
ms.order_type
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_system_items msi
where mfp1.plan_id = x_plan_id
and mfp1.sr_instance_id = x_instance_id
and mfp1.transaction_id = x_supply_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.pegging_id = mfp1.prev_pegging_id
and ms.plan_id = mfp2.plan_id
and ms.sr_instance_id = mfp2.sr_instance_id
and ms.transaction_id = mfp2.transaction_id
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
order by msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) desc;
select distinct ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)) critical_supply,
msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) start_date,
ms.order_type
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_system_items msi
where mfp1.plan_id = x_plan_id
and mfp1.sr_instance_id = x_instance_id
and mfp1.transaction_id = x_supply_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.sr_instance_id = mfp2.sr_instance_id
and ms.transaction_id = mfp2.transaction_id
and ms.transaction_id not in (
select mfq.number3
from msc_form_query mfq
where mfq.query_id = x_dem_op_query_id
and mfq.number1 = x_supply_id
and mfq.number2 is not null
)
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
order by msc_gantt_utils.getActualStartDate(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,
ms.source_supplier_id) desc;
select distinct ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
decode(g_end_demand_id, null, 0,
msc_gantt_utils.isCriticalSupply(x_plan_id,g_end_demand_id,
ms.transaction_id, ms.sr_instance_id)) critical_supply,
ms.new_wip_start_date start_date,
ms.order_type
from msc_supplies ms
where ms.plan_id = x_plan_id
and ms.sr_instance_id = x_instance_id
and ms.disposition_id = x_supply_id
-- and ms.order_type in (14,15,16,17,28)
and ms.transaction_id not in (select mgq.transaction_id
from msc_gantt_query mgq
where mgq.query_id = g_order_query_id
and mgq.transaction_id = ms.transaction_id)
order by ms.new_wip_start_date;
select distinct
decode(md.op_seq_num, 1, x_first_op, md.op_seq_num),
ms.transaction_id,
ms.sr_instance_id,
ms.organization_id,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id) critical_supply
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_demands md,
msc_form_query mfq
where mfp1.plan_id = x_plan_id
and mfp1.transaction_id = x_supply_id
and mfp1.sr_instance_id = x_instance_id
and mfq.query_id = x_end_peg_query_id
and mfp1.end_pegging_id = mfq.number1
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 ms.sr_instance_id = mfp2.sr_instance_id;
select distinct
decode(md.op_seq_num, 1, x_first_op, md.op_seq_num),
ms.transaction_id,
ms.sr_instance_id,
ms.organization_id,
msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
ms.transaction_id, ms.sr_instance_id) critical_supply
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms,
msc_demands md
where mfp1.plan_id = x_plan_id
and mfp1.transaction_id = x_supply_id
and mfp1.sr_instance_id = x_instance_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 ms.sr_instance_id = mfp2.sr_instance_id;
select number2 --op_seq_num
from msc_form_query mfq
where mfq.query_id = p_dem_op_query_id
and mfq.number1 = p_supply_id
and mfq.number3 = p_trans_id;
select mgq.row_index
from msc_gantt_query mgq
where mgq.query_id = p_qid
and mgq.transaction_id = p_supply_id
and mgq.op_seq_num = p_op_seq_num;
select count(*)
from msc_gantt_query mgq
where mgq.query_id = p_query
--and mgq.plan_id = p_plan
and mgq.sr_instance_id = p_inst
and mgq.transaction_id = p_trx
and rownum = 1;
select row_index
from msc_gantt_query mgq
where mgq.query_id = p_query
and mgq.sr_instance_id = p_inst
and mgq.transaction_id = p_trx;
select count(*)
from msc_gantt_query mgq
where mgq.query_id = p_query
and mgq.plan_id = p_plan
and mgq.sr_instance_id = p_inst
and mgq.organization_id = p_org
and mgq.inventory_item_id = p_item
and mgq.supplier_id = p_supp
and nvl(mgq.supplier_site_id, mbp_null_value) = nvl(p_supp_site, mbp_null_value)
and rownum = 1;
select node_type
from msc_gantt_query
where query_id = p_query
and row_index = p_index;
select parent_link
from msc_gantt_query
where query_id = p_query
and row_index = p_index;
update msc_gantt_query
set child_query_id = p_child_query_id
where query_id = p_parent_query_id
and transaction_id = l_trx_id ;
procedure updateParentLinkforCoprod(p_query_id number, p_row_index number,
p_parent_link varchar2, p_dependency_type number default null) is
begin
if ( p_parent_link is null ) then -- {
return;
put_line(' updateParentLinkforCoprod '
||' p_row_index '|| p_row_index
||' p_parent_link '|| p_parent_link);
update msc_gantt_query
set parent_link = decode(parent_link, null,p_parent_link,
parent_link|| COMMA_SEPARATOR ||p_parent_link),
dependency_type = p_dependency_type
where query_id = p_query_id
and row_index = p_row_index;
end updateParentLinkforCoprod;
procedure updateOpParentLink(p_query_id number, p_row_index number,
p_parent_link varchar2, p_dependency_type number default null) is
begin
if ( p_parent_link is null ) then -- {
return;
put_line(' updateOpParentLink '
||' p_row_index '|| p_row_index
||' p_parent_link '|| p_parent_link);
update msc_gantt_query
set parent_link = decode(parent_link,
null, p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space),
parent_link || FIELD_SEPERATOR || p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space)),
dependency_type = nvl(dependency_type,0)+1
where query_id = p_query_id
and row_index = p_row_index;
end updateOpParentLink;
procedure updateParentLink(p_query_id number, p_row_index number,
p_parent_link varchar2, p_dependency_type number default null) is
l_parent_row_type number;
put_line(' updateParentLink '
||' p_row_index '|| p_row_index
||' p_parent_link '|| p_parent_link);
update msc_gantt_query
set parent_link = decode(parent_link, null,p_parent_link,
parent_link|| COMMA_SEPARATOR ||p_parent_link),
dependency_type = p_dependency_type
where query_id = p_query_id
and row_index = p_row_index;
end updateParentLink;
updateParentLink(g_order_query_id, p_parent_index, g_node_index, null);
updateParentLink(g_order_query_id, p_parent_index, l_row_index, null);
updateOpParentLink(g_order_query_id, l_ops_intra_routing_cur.from_index,
l_ops_intra_routing_cur.to_index, l_ops_intra_routing_cur.dependency_type);
procedure insertOpIntoMFQ(p_instance_id number, p_org_id number, p_supply_id number) is
begin
insert into msc_form_query
(query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
number1, number2, number3, char9, date1)
select distinct
g_op_query_id,
trunc(sysdate), -1, trunc(sysdate), -1, -1,
p_supply_id, mrr.operation_seq_num,
msc_gantt_utils.isCriticalRes(g_plan_id, g_end_demand_id,
mrr.supply_id, mrr.sr_instance_id,
mrr.operation_seq_num, mrr.routing_sequence_id) critical_flag,
mro.operation_description op_desc,
mrr.start_date
from msc_resource_requirements mrr,
msc_routing_operations mro
where mrr.plan_id = g_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.organization_id = p_org_id
and mrr.supply_id = p_supply_id
and mrr.end_date is not null
and mrr.department_id <> -1
and nvl(mrr.parent_id,2) = 2
and mrr.plan_id = mro.plan_id (+)
and mrr.sr_instance_id = mro.sr_instance_id (+)
and mrr.routing_sequence_id = mro.routing_sequence_id (+)
and mrr.operation_sequence_id = mro.operation_sequence_id (+);
end insertOpIntoMFQ;
procedure insertOpJobFromMDIntoMFQ (p_first_op number,
p_instance_id number, p_org_id number, p_supply_id number) is
l_inst_id msc_gantt_utils.number_arr;
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_dem_op_query_id,
trunc(sysdate), -1, trunc(sysdate), -1, -1,
p_supply_id, l_op_seq_num(i), l_trans_id(i),
l_inst_id(i), l_org_id(i), l_critical_flag(i));
end insertOpJobFromMDIntoMFQ;
select distinct mfq.number2, mfq.number2
bulk collect into v_op, v_new_op
from msc_form_query mfq
where mfq.query_id = g_dem_op_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 = g_op_query_id
and mfq_mrr.number1 = p_supply_id);
select min(number2)
into v_dummy
from msc_form_query
where query_id = g_op_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 = g_op_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 = g_dem_op_query_id
and number1 = p_supply_id
and number2 = v_op(a);
select ms.order_type, ms.disposition_id
from msc_supplies ms
where ms.plan_id = p_plan
and ms.sr_instance_id = p_inst
and ms.transaction_id = p_trx;
updateParentLink(g_order_query_id, p_parent_index, nvl(l_parent_link, g_node_index) );
updateParentLinkforCoprod(g_order_query_id, p_parent_index, g_node_index);
insertOpIntoMFQ(l_inst_id(i), l_org_id(i), l_trans_id(i));
insertOpJobFromMDIntoMFQ(l_first_op, l_inst_id(i), l_org_id(i), l_trans_id(i));
updateParentLinkforCoprod(g_order_query_id, p_parent_index, l_row_index);
select row_index
from msc_gantt_query
where query_id = p_query
and sr_instance_id = p_inst
and transaction_id = p_trx;
select 1
from msc_resource_requirements
where plan_id = p_plan
and supply_id = p_supply
and nvl(parent_id,2) = 2;
updateParentLink(g_order_query_id, l_dup_row_index, l_parent_link);
updateParentLink(g_order_query_id, g_node_index, p_parent_index);
select transaction_id, organization_id, sr_instance_id, op_seq_id,
node_type, node_path
from msc_gantt_query
where query_id = p_query_id
and row_index = p_row_index;
select row_index, '1' dummy_sort
from msc_gantt_query
where query_id = p_query_id
and (node_type = JOB_NODE
or (node_type = COPROD_NODE and p_peg_dir = PEG_UP)
or (node_type = COPROD_NODE and p_peg_dir = PEG_DOWN)
or (node_type = COPROD_NODE and p_peg_dir = PEG_ORDERS))
and ((p_peg_dir = PEG_DOWN and row_index > p_row_index)
or (p_peg_dir = PEG_ORDERS and row_index > p_row_index)
or (p_peg_dir = PEG_UP and row_index < p_row_index ))
order by
decode(p_peg_dir, PEG_UP, row_index, dummy_sort) desc,
row_index asc;
select decode(p_peg_dir,
PEG_UP, node_level + PEG_UP,
PEG_DOWN, node_level + PEG_DOWN,
PEG_ORDERS, node_level + 1 )
from msc_gantt_query
where query_id = p_query_id
and row_index = p_row_index;
insertOpIntoMFQ(p_instance_id, p_org_id, p_supply_id);
insertOpJobFromMDIntoMFQ( l_first_op, p_instance_id, p_org_id, p_supply_id);
insertOpIntoMFQ(p_instance_id, p_org_id, p_supply_id);
updateParentLink(g_order_query_id, g_node_index, l_parent_index, null);
procedure updateNodeLevels(p_query_id number) is
l_min_node_level number;
select abs(min(node_level))
from msc_gantt_query
where query_id = p_query_id;
update msc_gantt_query
set node_level = l_min_node_level + node_level + 1
where query_id = p_query_id;
end updateNodeLevels;
select node_type, count(*)
from msc_gantt_query
where query_id = p_query_id
group by node_type;
select min(row_index)
from msc_gantt_query
where query_id = p_query_id;
select max(row_index)
from msc_gantt_query
where query_id = l_query ;
select res_instance_id,
nvl(serial_number, MBP_NULL_VALUE_CHAR) serial_number
from msc_dept_res_instances
where plan_id = g_plan_id
and sr_instance_id = l_inst_id
and organization_id = l_org_id
and department_id = l_dept_id
and resource_id = l_res_id;
select nvl(max(row_index),0)
into l_row_index
from msc_gantt_query
where query_id = p_query_id;
select node_type, node_path
from msc_gantt_query
where query_id = p_from_query_id
and row_index = p_from_index;
select nvl(max(row_index),0)
into l_row_index
from msc_gantt_query
where query_id = p_to_query_id;
select distinct sr_instance_id, organization_id, department_id, resource_id
bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
from msc_gantt_query mgq
where query_id = p_from_query_id
and node_type = RES_NODE
and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
or (l_node_type = RES_NODE and row_index = p_from_index) ))
and nvl(critical_flag, mbp_null_value) > 0;
select distinct sr_instance_id, organization_id, department_id, resource_id
bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
from msc_gantt_query mgq
where query_id = p_from_query_id
and node_type = RES_NODE
and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
or (l_node_type = RES_NODE and row_index = p_from_index) ));
select node_type, node_path
from msc_gantt_query
where query_id = p_from_query_id
and row_index = p_from_index;
select nvl(max(row_index),0) + 1
into l_row_index
from msc_gantt_query
where query_id = p_to_query_id;
select
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(ms.supplier_id, mbp_null_value),
nvl(ms.supplier_site_id, mbp_null_value)
bulk collect into v_inst_id, v_org_id, v_item_id, v_supp_id, v_supp_site_id
from msc_gantt_query mgq,
msc_supplies ms
where mgq.query_id = p_from_query_id
and mgq.row_index = p_from_index
and ms.plan_id = g_plan_id
and ms.sr_instance_id = mgq.sr_instance_id
and ms.transaction_id = mgq.transaction_id;
select mgq.res_instance_id
from msc_gantt_query mgq
where mgq.query_id = ll_query
and mgq.row_index = ll_index;
select nvl(max(row_index),0) + 1
into l_row_index
from msc_gantt_query
where query_id = p_to_query_id;
select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
bulk collect into v_inst_id, v_org_id, v_trx_id
from msc_gantt_query mgq,
msc_gantt_dtl_query mgdq,
msc_resource_requirements mrr
where mgq.query_id = p_from_query_id
and mgq.row_index = p_from_index
and mgq.query_id = mgdq.query_id
and mgq.row_index = mgdq.row_index
and mgdq.transaction_id = to_number(p_context_value)
and mrr.plan_id = g_plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.transaction_id = mgdq.transaction_id
and mrr.parent_id = 2;
select distinct mrir.sr_instance_id, mrir.organization_id, mrir.supply_id
bulk collect into v_inst_id, v_org_id, v_trx_id
from msc_gantt_query mgq,
msc_gantt_dtl_query mgdq,
msc_resource_instance_reqs mrir
where mgq.query_id = p_from_query_id
and mgq.row_index = p_from_index
and mgq.query_id = mgdq.query_id
and mgq.row_index = mgdq.row_index
and mgdq.transaction_id = to_number(p_context_value)
and mrir.plan_id = g_plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and mrir.serial_number = mgq.serial_number
and mrir.res_inst_transaction_id = mgdq.transaction_id
and mrir.parent_id = 2;
select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
bulk collect into v_inst_id, v_org_id, v_trx_id
from msc_gantt_query mgq,
msc_gantt_dtl_query mgdq,
msc_resource_requirements mrr
where mgq.query_id = p_from_query_id
and mgq.row_index = p_from_index
and mgq.query_id = mgdq.query_id
and mgq.row_index = mgdq.row_index
and mrr.plan_id = g_plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date,
mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between to_date(p_context_value,FORMAT_MASK) and to_date(p_context_value2,FORMAT_MASK)
or msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between to_date(p_context_value,FORMAT_MASK) and to_date(p_context_value2,FORMAT_MASK))
and mrr.parent_id = 2;
select distinct msr.sr_instance_id, msr.organization_id, msr.supply_id
bulk collect into v_inst_id, v_org_id, v_trx_id
from msc_gantt_query mgq,
msc_supplier_requirements msr
where mgq.query_id = p_from_query_id
and mgq.row_index = p_from_index
and msr.plan_id = g_plan_id
--and msr.sr_instance_id = mgq.sr_instance_id
--and msr.organization_id = mgq.organization_id
and msr.inventory_item_id = mgq.inventory_item_id
and msr.supplier_id = mgq.supplier_id
and msr.supplier_site_id = mgq.supplier_site_id
and trunc(msr.consumption_date) between to_date(p_context_value,FORMAT_MASK)
and to_date(p_context_value2,FORMAT_MASK);
select count(*)
from msc_gantt_query
where query_id = p_query_id;
update msc_gantt_query
set is_fetched = SYS_NO,
row_flag = SYS_NO
where query_id = p_query_id;
delete from msc_gantt_dtl_query
where query_id = p_query_id;
update msc_gantt_query
set is_fetched = SYS_NO,
row_flag = SYS_NO
where query_id = p_query_id;
delete from msc_gantt_dtl_query
where query_id = p_query_id;
update msc_gantt_query
set is_fetched = SYS_YES
where query_id = p_query_id
and row_flag = SYS_YES ;
update msc_gantt_query
set is_fetched = SYS_NO,
row_flag = SYS_NO
where query_id = p_query_id;
delete from msc_gantt_dtl_query
where query_id = p_query_id;
update msc_gantt_query
set is_fetched = SYS_NO,
row_flag = SYS_NO
where query_id = p_query_id;
delete from msc_gantt_dtl_query
where query_id = p_query_id;
update msc_gantt_query
set is_fetched = SYS_YES
where query_id = p_query_id
and row_flag = SYS_YES ;
procedure updateResUnitsDirectly(p_query_id number,
p_node_type number, p_inst_id number, p_trx_id number,
p_assigned_units_hours number,
p_return_status OUT NOCOPY varchar2,
p_out OUT NOCOPY varchar2) is
l_res_hours number;
select count(*)
into l_child_count
from msc_resource_requirements mrr,
msc_resource_instance_reqs mrir
where mrr.plan_id = g_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_trx_id
and mrr.plan_id = mrir.plan_id
and mrr.sr_instance_id = mrir.sr_instance_id
and mrr.organization_id = mrir.organization_id
and mrr.department_id = mrir.department_id
and mrr.resource_id = mrir.resource_id
and nvl(mrr.parent_id,2) = 2;
select mrr.resource_hours
into l_res_hours
from msc_resource_requirements mrr
where mrr.plan_id = g_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_trx_id
for update of mrr.resource_hours nowait;
update msc_resource_requirements
set status =0,
applied=2,
assigned_units = p_assigned_units_hours
where plan_id = g_plan_id
and transaction_id = p_trx_id
and sr_instance_id = p_inst_id;
end updateResUnitsDirectly;
procedure updateResHoursDirectly(p_query_id number,
p_node_type number, p_inst_id number, p_trx_id number,
p_resource_hours number, p_return_status OUT NOCOPY varchar2,
p_out OUT NOCOPY varchar2) is
l_res_hours number;
select mrr.resource_hours
into l_res_hours
from msc_resource_requirements mrr
where mrr.plan_id = g_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_trx_id
for update of mrr.resource_hours nowait;
update msc_resource_requirements
set status =0,
applied=2,
resource_hours = p_resource_hours
where plan_id = g_plan_id
and transaction_id = p_trx_id
and sr_instance_id = p_inst_id;
select mrir.resource_instance_hours
into l_res_hours
from msc_resource_instance_reqs mrir
where mrir.plan_id = g_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_trx_id
for update of mrir.resource_instance_hours nowait;
update msc_resource_instance_reqs
set status =0,
applied=2,
resource_instance_hours = p_resource_hours
where plan_id = g_plan_id
and res_inst_transaction_id = p_trx_id
and sr_instance_id = p_inst_id;
end updateResHoursDirectly;
select daily_resource_constraints,
weekly_resource_constraints,
period_resource_constraints, plan_type
from msc_plans
where plan_id = p_plan_id;
select nvl(nvl(curr_ds_enabled_flag,ds_enabled_flag), sys_no) ds_enabled_flag
from msc_plan_organizations
where plan_id = p_plan_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_resource_requirements mrr,
msc_form_query mfq
where mfq.query_id = v_mfq_from_query_id
and mrr.plan_id = p_plan_id
and mrr.sr_instance_id = mfq.number2
and mrr.transaction_id = mfq.number1
and mfp.plan_id = mrr.plan_id
and mfp.transaction_id = mrr.supply_id
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_resource_instance_reqs mrir,
msc_form_query mfq
where mfq.query_id = v_mfq_from_query_id
and mrir.plan_id = p_plan_id
and mrir.sr_instance_id = mfq.number2
and mrir.res_inst_transaction_id = mfq.number1
and mfp.plan_id = mrir.plan_id
and mfp.transaction_id = mrir.supply_id
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = l_inst_id
and mrr.organization_id = l_org_id
and mrr.department_id = l_dept_id
and mrr.resource_id = l_res_id
and ( nvl(mrr.firm_start_date, mrr.start_date) between p_date1 and p_date2
or nvl(mrr.firm_end_date, mrr.end_date) between p_date1 and p_date2
or ( nvl(mrr.firm_start_date, mrr.start_date) <= p_date1
and nvl(mrr.firm_end_date, mrr.end_date) >= p_date2) ) --5456033 bugfix
and mfp.plan_id = mrr.plan_id
and mfp.transaction_id = mrr.supply_id
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = l_inst_id
and mrir.organization_id = l_org_id
and mrir.department_id = l_dept_id
and mrir.resource_id = l_res_id
and mrir.res_instance_id = l_res_instance_id
and mrir.serial_number = l_serial_number
and ( nvl(mrir.start_date, mrir.start_date) between p_date1 and p_date2
or nvl(mrir.end_date, mrir.end_date) between p_date1 and p_date2
or ( nvl(mrir.start_date, mrir.start_date) <= p_date1
and nvl(mrir.end_date, mrir.end_date) >= p_date2) )
and mfp.plan_id = mrir.plan_id
and mfp.transaction_id = mrir.supply_id
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_resource_requirements mrr,
msc_form_query mfq
where mfq.query_id = v_mfq_from_query_id
and mrr.plan_id = p_plan_id
and mrr.sr_instance_id = mfq.number2
and mrr.transaction_id = mfq.number1
and mfp.plan_id = mrr.plan_id
and mfp.transaction_id = mrr.supply_id
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
mfp2.demand_id, mfp2.sr_instance_id
from msc_full_pegging mfp2,
msc_full_pegging mfp,
msc_form_query mfq
where mfq.query_id = v_mfq_from_query_id
and mfp.plan_id = p_plan_id
and mfp.sr_instance_id = mfq.number2
and mfp.transaction_id = mfq.number1
and mfp2.plan_id = mfp.plan_id
and mfp2.pegging_id = mfp.end_pegging_id;
select min(msr.consumption_date) start_date
from msc_supplier_requirements msr
where msr.plan_id = g_plan_id
and msr.supply_id = p_trx_id;
select min(msr.consumption_date) start_date
from msc_supplier_requirements msr,
msc_resource_requirements mrr
where mrr.plan_id = g_plan_id
and mrr.transaction_id = p_trx_id
and msr.plan_id = mrr.plan_id
and msr.sr_instance_id = mrr.sr_instance_id
and msr.supply_id = mrr.supply_id;
select min(nvl(mrr.firm_start_date,mrr.start_date)) start_date
from msc_resource_requirements mrr
where mrr.plan_id = g_plan_id
and mrr.supply_id = p_trx_id;
select min(nvl(mrr.firm_start_date,mrr.start_date)) start_date
from msc_resource_requirements mrr
where mrr.plan_id = g_plan_id
and mrr.transaction_id = p_trx_id;